Need some help on table locking with VB.NET | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need some help on table locking with VB.NET

Hi, I’m quite inexperienced with SQL Server 2000 but I have to write an application on it for my job, in VB.NET. Basically what I will have is the following: Several client computers will poll a specific database table each x seconds. It’s in fact a table which contains jobs that the client computers must execute. What I need to do, as described in the concept of the application by our analyzer, is when a client polls the table, it locks the full table (so no other client can read nor change data anymore), selects the records it needs, updates those records (setting a field from busy = 0 to busy = 1), and then unlocks the table so that other clients can access it to poll for their collection of jobs which are not busy. How can this be achieved ?
I’m using the SQLConnection object from .NET. I have no experience in any Transact-SQL syntax. The only thing I do is writing plain SQL queries from my .NET code to be executed by the SQLConnection object (with a DataAdapter etc.). Thank you for your answers ! Kind regards, Mathieu
well ado provides a locking machanism called adlockpessimistic which locks the table as soon as it is selected.
]]>