Locking a table row

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

Locking a table row

Post by mskeels » Tue 10 Dec 2013 17:52

All,

My object is to avoid ungraceful exception handling during record edit in SQL Server.

I want both to recover from a locking error, and to retry or wait a period of time if somehow editing fails in my program.

I have seen some example code snippets but so far none I have found are explicit enough to get me on the right track.

My question:

Suppose I select pessimistic locking on a TMSTable component connected to my SQL table.

I then try to put the dataset into edit mode.

...
MyTable.Edit;
...

But another app somewhere currently has the record in edit mode, or locked, or otherwise unavailable.

What happens in my program when I initiate the edit?

Will it throw an error?

Will it wait for a timeout?

Something else?

Thanks,
Mark

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Locking a table row

Post by AlexP » Wed 11 Dec 2013 09:20

Hello,

When using the pessimistic lock mode, on editing attempt the following query will be executed:

Code: Select all

SELECT * FROM table
WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
WHERE
  KeyField = ?
In case if this record is already locked, you will get an error "lock request time out period exceeded", you can handle it in a try...except block by its code - 1222, i.e.:

Code: Select all

  try
    MSTable2.Edit;
  except
    on E: EMSError do
      case e.ErrorCode of
        1222: ShowMessage('The record is edited by another user.');
        else ShowMessage(e.Message)
      end;
  end;

Pavol
Posts: 1
Joined: Thu 15 May 2014 07:10

Re: Locking a table row

Post by Pavol » Thu 15 May 2014 07:36

Hi, I have subsidiary question: How can I get the user which has locked the record?
Thanks.

Post Reply