Optimistic locks, pessimistic, and deadlocks

Lock: we know that the most common method to handle concurrent access by multiple users is locking. When a user locks an object in the database, other users cannot access the object. The impact of locking on concurrent access is reflected in the lock granularity. For example, the lock placed on a table restricts concurrent access to the entire table; the lock placed on the data page limits access to the entire data page; the lock placed on the row only limits concurrent access to the row. The visible row lock has the minimum granularity, the best concurrent access, the maximum page lock granularity, and the table lock is between two.

There are two types of locks: Pessimistic locks and optimistic locks. Pessimistic locks assume that other users attempt to access or change the objects you are accessing and changing have a high probability. Therefore, in the pessimistic locks environment, lock the object before you begin to change it, and release the lock until you submit the changes. The pessimistic defect is that whether it is a page lock or a row lock, the lock may take a long time. This may limit the access of other users for a long time, that is, the concurrent access of the pessimistic lock is not good. In contrast to the pessimistic lock, the optimistic lock considers that the probability of other users attempting to change the object you are changing is very small, so the optimistic lock does not lock the object until you are ready to submit the change, it is not locked when you read or change this object. It can be seen that optimistic locks take less time to lock than pessimistic locks. Optimistic locks can achieve better concurrent access performance with a larger lock granularity. However, if the second user reads the object just before the first user submits the change, the database will find that the object has changed when he completes the change, in this way, the second user has to re-read the object and make changes. This indicates that the number of times concurrent users read objects increases in the optimistic lock environment.

From the perspective of database vendors, it is better to use optimistic page locks, especially in batch operations that affect a lot of rows, this reduces resource requirements and improves database performance. Then consider clustered indexes. Records in the database are stored in the physical order of clustered indexes. If a page lock is used, when two users access and change adjacent two rows on the same data page at the same time, one user must wait for another user to release the lock, which significantly reduces system performance. Like most relational databases, Interbase uses optimistic locks and read locks are shared, while write locks are exclusive. You can place a read lock on a read lock, but you cannot place a write lock. You cannot place any lock on the write lock. Lock is an effective method to solve concurrent access by multiple users.

Deadlock: a deadlock occurs when two or more jobs have a resource lock, but other jobs attempt to lock the resource, which permanently blocks each other. For example:

1. Transaction A gets the share lock of data column 1.

2. Transaction B acquires the share lock of data column 2.

3. Transaction a now requires an exclusive lock on column 2, but it will be blocked until transaction B completes and releases the share lock on column 2.

4. Transaction B now requires an exclusive lock on column 1, but it will be blocked until transaction a completes and releases the share lock on column 1.

After transaction B is completed, transaction A can be completed, but transaction B is blocked by transaction. This situation is also called cyclic dependency ). Transaction a depends on transaction B, and transaction B Closes this loop because it depends on transaction.

For example, the following operation will lead to a deadlock, and the two connections will block the update of each other.

Connection 1:

Begin tran

Select * from MERs

Update MERs set companyName = companyName

Waitfor delay '00: 00: 05'

Select * from employees

-Because employees is locked by connection 2, it will be blocked here.

Update employees set lastname = lastname

Commit tran

 

Connection 2:

Begin tran

Select * from employees

Update employees set lastname = lastname

Waitfor delay '00: 00: 05'

Select * from MERs

-- Because MERs Mers is locked by connection 1, it will be blocked here.

Update MERs set companyName = companyName

Commit tran

 

When SQL Server encounters a deadlock, one of the transactions is automatically killed, and the other ends normally (commit or rollback ).

The error code returned by SQL Server for the connection killed is 1205. The error message is:

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.