lock table -commit rollback?
Posted on 2000-03-13
I asked this question in MSSQL section of exchange, but sofar not much help from there... so perhaps someone here has the answer:
Hi, I am writing a program that needs to farm out work to various
operators. Each operator is going to work on one record at a time,
they will click next, and the next record that is of status 1 will
be displayed to them, while they are modifying this record the status
needs to be set to 2, such that another operator does not also work
on the same record: This is a simplified version of what is going on
but it illustrates my problem:
There is a table called work
Firstly I run "Select * from work where status = 1 order by ID" =RecordSet
This brings back a list of work to be done.
Now I need to run an update:
"update work set status = 2 where ID = IDofFirstInRecordSet"
The problem is I need both queries to run asif they were one query,
ie atomic...(otherwise another operator might just run the select statement
before the original operator has updated the record and thus both of them
work on the same record!!)
I don't know how to achieve this.. can any one help