Record-/table-locking

[formi]

SQL Server 2008, UniDac-components.
I have to deliver a unique value (as a document-number with a specific format). So my idea is to lock a record (or the whole table), to read then last value, to modify it and to unlock the record / table. I tried it with
try
Connection.StartTransaction
Table.Open
Table.Lock
do by job
Table.Unlock
Table.Close
except
Showmessage('Sorry, table is locked ...');
end;

To test it I made two buttons, two tables and the code above in the OnClick-Event for each table. But there is never an excption fired. How to do it? Thanks for your help, Peter

[AlexP]

Hello,

This error message occurs only in case of an attempt to perform simultaneous access to a locked table.
This error message occurs only in case you do not complete an already started transaction, before an attempt to access the table.

[formi]

And how can I solve my task? Exclusive acces on a record or on a whole table?
Thanks Peter

[AlexP]

hello,

If you don't need to change all records in the table, it is irrational to lock the whole table, it is sufficient to lock only the record that requires changing.

[formi]

Thanks, but I don't know how to do it. Please give an example, Peter

[AlexP]

hello,

To lock the record you are editing, set the LockMode property to lmPessimistic, in this case calling the Edit method will call the following command:

Code: Select all

SELECT * FROM Table
WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
WHERE
  id = ?
:Old_id(Integer,IN)=N

which will lock the current record

[formi]

Thanks for your help, that works for me, Peter

[AlexP]

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.