What is sp_lock stored procedure?

What is sp_lock stored procedure?

Rating:

Sp_lock is a system stored procedure used to return the following information about locks held on the SQL 2000 Server:

The SQL Server 2000 server process ID number (SPid).

The database identification number (DBid) requesting a lock.

The object identification number (ObjId) of the object requesting a lock.

The index identification number (IndId).

The lock mode (Mode).

The type of lock (Type).

Lock Mode
The lock modes used by SQL Server 2000 are shared locks (S), update locks (U), exclusive locks (X), and intent locks, which could be intent shared locks (IS), intent exclusive locks (IX), and shared with intent exclusive locks (SIX).

Shared locks allow concurrent transactions to read a resource by using the SELECT statement. While a shared lock exists on the resource, no other transactions can modify the data. Shared locks are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or a higher level, or a locking hint is used to retain the shared locks for the duration of the transaction.

Update locks prevent a common form of deadlock from occurring. A typical update pattern consists of a transaction reading a record, acquiring a shared lock on the resource, and then modifying the row, which requires lock conversion to an exclusive lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, this may cause a deadlock This is because both the transactions are converting to exclusive locks, and they are each waiting for the other transaction to release its shared lock. To avoid this problem, only one transaction is allowed to obtain an update lock on a resource at a time. If a transaction modifies a resource, the update lock is converted to an exclusive lock. Otherwise, the lock is converted to a shared lock.

Exclusive locks prevent access to a resource by other transactions. Therefore, these transactions are prevented from reading or modifying data locked with an exclusive lock.

Intent locks indicate that SQL Server 2000 wants to acquire a shared lock or exclusive lock on some of the resources lower down in the hierarchy. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock on the table. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

The Types of Locks
The type of lock indicates the type of the resource that is currently locked.
RID: Row identifier is used to lock a single row within a table.
KEY: Row lock within an index to protect key ranges in serializable transactions.
PAG: Data or index page is locked.
EXT: Contiguous group of eight data pages or index pages are locked.
TAB: Entire table, including all data and indexes is locked.
DB: Database is locked.

Rating: