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
mskeels
Posts: 16
Joined: Tue 06 Mar 2012 15:08

Record Locking

Post by mskeels » Fri 13 Mar 2015 18:57

All,

Please help me, here is my question.

I thought the below code would lock only one row in a MSSQL table:

But it appears to lock multiple rows. Since the users of the client app are placing orders simultaneously, they often, but not always, lock up each others clients.

How can I begin to troubleshoot this?

Using DBMonitor I can see that the transaction, lock and commit seem to be working correctly as far as I can tell.

*******
try //try except
if not fdmSQL.msCROE_Ord.Connection.InTransaction then
begin
fdmSQL.msCROE_Ord.Connection.StartTransaction;
end;

fdmSQL.msCROE_Ord.Connection.ExecSQL('SET LOCK_TIMEOUT 2000',[]);
fdmSQL.msCROE_Ord.Lock(ltUpdate);
fdmSQL.msCROE_Ord.RefreshRecord;
......


*******
Thanks,
mark

azyk
Devart Team
Posts: 1058
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Record Locking

Post by azyk » Wed 18 Mar 2015 09:12

When calling the Lock method, a SELECT SQL query is executed with the option 'WITH (UPDLOCK, ROWLOCK, HOLDLOCK)', in order to lock the current record. Check how many records are returned and locked by this query. For this, check the WHERE query. You can learn which query is executed using DBMonitor.

Post Reply