Locking a table row

[mskeels]

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]

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]

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