Record locking

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 35
Joined: Thu 11 Nov 2004 08:35

Record locking

Post by Sawlmgsj » Sun 25 Dec 2005 17:32

I have two users editing the same record at the same time. One saves the record and then the other saves the record a few seconds later. Is there a way to indicate to the second user that updates have just been done by the first user.

This was fairly automatic with my program using Ms Access, but I do not know how to do it with SQL and SDAC. (I have experimented with the isolation level but cannot see how to make this work.)


Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Mon 26 Dec 2005 10:51

SQL Server doesn't provide such functionality.


Post by Abadia » Sat 31 Dec 2005 19:18

Do this:
1 - StartTransaction
2 - set lock time out to 0
3 - select the register with lock hint UPDLOCK like this:
SELECT au_lname FROM authors WITH (UPDLOCK)
if no error happens, then the register is locked
4 - select the register with all fields and join as you need.

ps: the lock is released only end of transaction (commit or rollback)

Posts: 39
Joined: Fri 16 Dec 2005 07:41

Post by tinof » Mon 02 Jan 2006 16:04


find the post of abadia very interesting, please allow me a question :
I have learned (in theory and praxis too (Pervasive.SQL Database) ) "Never let a transaction active while user input! " - the user starts editing, goes away (because it's breakfast :) ) and the records remain locked. I have seen very 'blocked' databases programming this way. As i said - using Pervasive.SQL. How looks this with SQL - Server ? What exactly does the lock timeout ?
Thanks for avice



Post by Abadia » Thu 05 Jan 2006 12:28


The lock time out on SQLServer is only amount of time wait for a locked record. When it's 0, means: don't wait.

About trasactions:
There are two logics. Optimistic and pessimistic. In your instance damand for pessimistic logic. In case of need (because it's breakfast :lol:) you can implement a timer idle function for auto-rollback transaction and free locked register.


Post Reply