SQL Engine automatically locks database objects accessed by each transaction, to prevent or limit simultaneous access by other transactions. SQL Engine requests a lock on the data before a transaction begins and releases the lock when the transaction is complete.
| Lock Level | Description |
|---|---|
| Read | Prevents write and exclusive locks. Generally caused by a SELECT. Ensures consistency during read operations. Several users may hold concurrent read locks on the same data, during which no modification of the data is permitted. |
| Write | Prevents other read, write, and exclusive locks. Generally caused by an INSERT, UPDATE, or DELETE statement. Enables users to modify data while locking out all other users except those using access locks. Until a write lock releases, no new read or write locks are allowed. |
| Exclusive | Prevents any other type of concurrent access. Generally caused by statements that make structural changes, for example, using an ALTER TABLE statement to add, drop, or change a column, when the statement uses the LOCKING modifier. Exclusive locks are the most restrictive type of lock. When you hold an exclusive lock, other users cannot read or write to the data. |
| Access | Prevents exclusive locks only. Caused by the LOCKING…FOR ACCESS
or LOCKING…FOR LOAD COMMITTED locking modifier. When you use the LOCKING…FOR ACCESS locking modifier you can read data while changes are in process. However, if you use this modifier, concurrent requests may access data that has not been committed, causing inconsistent results. Use this modifier for decision support on large tables that are updated only by small, single-row changes. When you use the LOCKING…FOR LOAD COMMITTED locking modifier on load-isolated tables, you can read committed rows while concurrent changes are in process. Unlike LOCKING…FOR ACCESS, this modifier returns only committed data. For more information on Load Isolation, see About Reading Committed Data While Loading to the Same Table. |