Record Locking

[mskeels]

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]

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.