Locks the current records to prevent multiple users' access to it.
procedure Lock(LockType: TMSLockType); reintroduce; overload;
This method locks the current record in dataset to prevent multiple users' access to it.
Record lock can be performed only within a transaction. If an application cannot update/lock a record because it has already been locked, it will wait until the lock is released. When the server lock timeout has expired, but lock is not acquired, an exception will be raised. Lock is released when the transaction is committed/rolled back.
You should also be aware of the Lock Escalation mechanism of SQL Server using locking in SDAC. Locking multiple records in the same table may lead to the locking of a whole table. This will avoid the server's resources overrun.
Note There is an optimization for exclusive locks - SQL Server checks whether data has been changed since the transaction was started. If not, then a lock request is ignored. For more information see this topic of MSDN.
To avoid this issue, you can refresh only locked record:
if not MSQuery.Connection.InTransaction then // check whether the transaction is active MSQuery.Connection.StartTransaction; // run the transaction // setup how much time to wait before raising an exception // if the record is already locked by someone else MSQuery.Connection.ExecSQL('SET LOCK_TIMEOUT ' + IntToStr(StrToInt(edLockTimeout.Text)*1000),); MSQuery.Lock(ltExclusive); // perform exclusive lock MSQuery.RefreshRecord; // make sure that the record is locked