Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 LOCKING issue

Author  Topic 

Posting Yak Master

109 Posts

Posted - 2002-03-20 : 17:26:12
Salute to All..
I have this issue..
I am desgining an Invoices Settlement form in VB..I retirve invoices in a command..add them to an array then when the user decides to commit I will open a command to settle the invoices..
HOW CAN I MAKE sure the nobody settled an invoice between the first user's retreival and its settlement?
i mean how can I LOCK THE RECORD and then RELEASE THE LOCK? check it out..then check it in??..

If I am to do it,
I will, and NOW ..


12543 Posts

Posted - 2002-03-20 : 21:32:37
There are several ways to do this depending on what effect you want.

Usually for updating after the user has entered data you will just reject the update if the record has changed.
Just put a timestamp on the table - retrieve the timestamp with record send the timestamp back to the update SP and if it does not match the record then reject the update and tell the user to re-enter. This gets round the problem of the user retrieving the data for update then going away and does not hold any info in the database about the user.

You can lock the records for update without affecting reads... by hoding the record primary key and the user spid and logon time in a resource locking table. Only allow users to lock records that are not in the table (by adding the entry). You can tell if the user has crashed by checking the spid/logon time.

Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

- Advertisement -