Locks and Record Locking

Lock Types

Record or object locking (i.e. locking records in the object table) is used to restrict other user actions over the same record or object. There are two types of locks:

READ lock - if you place this lock on a record, no other user may overwrite any column of this record.
WRITE lock - if you place this lock on a record, no other user may overwrite any column of this record and moreover cannot prevent you from overwriting attribute values of this record (by placing a READ lock on the record).

There can be more read locks placed by different users on the same record or a single write lock and no other locks from different user. An attempt to place a lock that disagrees with this rule results in the NOT_LOCKED (136) error.

A client may explicitly place and remove locks, thus restricting other client access to certain data. If a client places more locks on a object, it has to remove them all later.

Also the server automatically places temporary locks valid until the end of a transaction in these cases:

for each record value update, or each record delete or insert a read lock is placed on that record,
if a higher level of transaction isolation than READ COMMITTED is set on the server, then the server automatically places read locks on read data,
if a user removes its own explicit write lock, the server places a temporary write lock on the same object.

All these temporary locks are removed when the transaction is complete.

You can get an overview of live locks and their owners by calling the _iv_locks system query or from the Monitor window, Locks tab. Locks that prevent normal operation may be removed by a configuration administrator from this window.

Waiting for Freeing Resources

A client request processing may be postponed by the SQL server until the resources required for this request are free. This happens in these following cases:

if the transaction isolation level prevents working with an object until a transaction of another client, who utilized the same object, is complete - see more;
if a client can't work with an object, because another client explicitly locked it.

The client may specify how long it wants to wait. When the time runs out and the object is not free, the operation results in the NOT_LOCKED error (and if the tracing TRACE_LOCK_ERROR is on, details will be written to the log). The waiting limit time is set for each client separately.

The time is set in tenths of second. The -1 value means unlimited waiting. You can use also the system property @@WAITING to set the waiting limit. You can this property from the client by calling the waiting API function. The waiting limit for newly connected clients is set according to the LockWaitingTimeout server property (can be also set in the Runtime parameters window on the Operation tab). The default value is 150 (15 seconds).

Waiting limit can be delaying in some cases (e.g. work with object designers), therefore the error NOT_LOCKED will raise immediately in these cases.

System Triggers

602SQL Server

Threads and Thread Synchronization