Fundamentals of Database Locks

Fundamentals of Database Locks

In order to ensure the data is finished and consistent, the database system adopts the lock to realize the isolation of the transaction. The basic theory of locks used in various large databases is consistent, but there are differences in the implementation.

From a relationship locked by a concurrent transaction, it can be divided into shared locks and exclusive locks. Different from locked objects, you can generally be divided into table locks and row locks.

Lock

A shared lock is used to read a data operation, which is non-exclusive, allowing other transactions to read its locked resource at the same time, but does not allow other transactions to update it.

An exclusive lock is also called an exclusive lock, which is suitable for modifying data. The resources it locks, other transactions cannot be read, and cannot be modified.

When a transaction accesses a database resource, if you execute a select statement, you must first obtain a shared lock if you perform an insert,update , or Delete statement, you must obtain an exclusive lock that locks the resource that is being manipulated.

When the second transaction also accesses the same resource, execute select statement, You must also obtain a shared lock, if you do insert , Span style= "FONT-FAMILY:CALIBRI;" >update or delete statement, you must also obtain an exclusive lock. At this point, depending on the type of lock that has been rotated on the resource, the second transaction should wait for the first transaction to unlock the corresponding resource, or the lock can be acquired immediately.

Locks already placed on the resource	Second transaction for read operation	The second transaction makes an update operation
No	Get a shared lock now	Get an exclusive lock now
Shared locks	Get a shared lock now	Waits for the first transaction to unlock a shared lock
Exclusive lock	Waits for the first transaction to unlock an exclusive lock	Waits for the first transaction to unlock an exclusive lock

1Shared Locks

1, lock conditions: When a transaction executes a select statement, the database system assigns a shared lock to the transaction to lock the queried data.

2.Unlocking Condition: By default, the database system immediately unlocks the shared lock after the data is read. For example, when a transaction executes a query "select * from accounts" statement, the database system first locks the first row, reads, unlocks the first row, and then locks the second row. This allows other transactions to update the unlocked rows in the accounts table at the same time during a transactional read operation .

3. Compatibility with other locks: if a shared lock is placed on the data resource, the shared and update locks can be placed again.

4, concurrency performance: with good concurrency performance, when the data is placed in a shared lock, you can also place a shared lock or update lock. So concurrency performance is good.

2Exclusive Lock

1, lock conditions: When a transaction executes an insert,update , or delete statement, the database system automatically The data resource manipulated by the SQL statement uses an exclusive lock. If the data resource already has another lock (any lock) present, it cannot be placed in an exclusive lock.

2. Unlocking conditions: Exclusive locks need to wait until the end of the transaction to be dismissed.

3, Compatibility: Exclusive lock can not be compatible with other locks, if the data resources have been added to the exclusive lock, you can no longer place other locks. Similarly, if other locks have been placed on the data resource, then no exclusive locks can be placed.

4, concurrency performance: Needless to say, the worst. Only one transaction is allowed to access the locked data, and if other transactions require access to that data, you must wait for the previous transaction to end, unlock the exclusive lock, and the other transaction will have access to the data.

3Update lock

The initialization phase of the update lock is used to lock resources that might be modified, which avoids the deadlock caused by the use of shared locks. For example, for the following update statement:

UPDATE accounts SET balance=900 WHERE id=1

The update operation takes two steps:

L read the record with ID 1 in the accounts table .

L Perform the update operation.

If you use a shared lock in the first step, and then you upgrade the lock to an exclusive lock in the second step, a deadlock may occur. For example, two transactions acquire a shared lock for the same data resource, and then both have to escalate the lock to an exclusive lock, but wait for another transaction to unlock the shared lock in order to escalate to an exclusive lock, which creates a deadlock.

The update lock has the following characteristics:

L lock-in condition: When a transaction executes an update statement, the database system allocates a renewal lock for the transaction first.

L Unlock Condition: When the data is read and the update operation is performed, the update lock is upgraded to an exclusive lock.

l Compatibility with other locks: update locks are compatible with shared locks, that is, one resource can place update and shared locks at the same time, but a maximum of one update lock is placed. Thus, when multiple transactions update the same data, only one transaction can obtain an update lock, and then upgrade the update lock to an exclusive lock, other transactions must wait until the end of the previous transaction to obtain an update lock, which avoids deadlocks.

Concurrency performance: Allows multiple transactions to read the locked resource at the same time, but does not allow other transactions to modify it.

Fundamentals of Database Locks