SQL Server Performance

Need some help on table locking with VB.NET

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by msouphy, Apr 7, 2005.

  1. msouphy New Member

    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
  2. ranjitjain New Member

    well ado provides a locking machanism called adlockpessimistic which locks the table as soon as it is selected.

Share This Page