Checking Out Data to Client Users

I'm trying to resolve an issue that I've run into in my current system.

I have about 10 clients accessing a SQL Server several times per minute (every 10-20 seconds). To have an individual find the next record, I follow the following process:

1. Select the value of the next record in the database to be checked out.
2. Update the record to show that it is checked out to the user.
3. Select the data in the record to display to the user.
3. Update the record to show any changes and to check the record back in after the user edits it.

My issue is that clients can execute at the same time. Right now, with just SQL statements, two clients can get the same value in step #1. That makes them select the same record for editing. Can I use T-SQL to prevent this from happening? If I use a transaction, will the SQL Server 2005 queue up the transactions, or could I still get the same problem of opening up the same record?

Thanks!
Drew

Hi,

this depends on your update clause for setting the "inUseFlag". If you code it the following way there will be no concurrency with any user:

UPDATE SOMETABLE T
SET Checkout = 'CheckOutorWhatever'
OUTPUT INSERTED.IDColumn
FROM SomeTable T
WHERE EXISTS
(
SELECT * FROM SomeTable TSub
WHere T.IDColumn = TSub.IDColumn
AND Checkout IS NULL
)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks! I didn't know about the OUTPUT statement and couldn't find anything through Google.