Pessimistic lock and optimistic lock

1. transaction isolation

Transaction isolation is a function provided by the database.

SQL Server uses the SET transaction isolation level statement to set the transaction isolation level:

SET transaction isolation level

{Read uncommitted

| Read committed

| Repeatable read

| Snapshot

| Serializable

}

[;]

Read committed is the default isolation level of SQL Server.

1.1 read uncommitted

Read uncommitted transactions can read records that have been modified but not committed.

The read uncommitted transaction generates dirty read (dirty read ).

The read uncommitted transaction has the same effect as the SELECT statement plus nolock. It is the least restrictive among all isolation levels.

1.2 Read committed

Read committed transactions cannot read records that have been modified but not committed.

Read committed is the default isolation level of SQL Server.

1.3 Repeatable read

Repeatable read transactions cannot read records that have been modified but not committed. before the transaction is completed, no other transactions can modify the records currently read by the transaction.

Other transactions can still Insert new records, but must comply with the search criteria of the current transaction-this means that when the current transaction re-queries the record, there will be phantom read ).

1.4 Snapshot

The records read by any statement in the snapshot transaction are the data at the startup of the transaction.

This is equivalent to a dedicated "snapshot" generated by the database for the transaction when the transaction is started ".

In the current transaction, no data changes made by other transactions after the current transaction is started.

The Snapshot transaction does not require locking when reading the record. The snapshot transaction that reads the record does not lock other transaction write records, and the transaction that writes the record does not lock the snapshot transaction to read data.

1.5 serializable

Serializable transactions produce the following results:

1. The statement cannot read records that have been modified but not submitted by other transactions.

2. Before the current transaction is completed, other transactions cannot modify the records read by the current transaction.

3. Before the current transaction is completed, the index key value of the new record inserted by other transactions cannot be within the index key range read by any statement of the current transaction.

The effects of adding holdlock to a serializable transaction and a SELECT statement are the same.

2 read committed and Repeatable read

Read committed and Repeatable read are the two most common transactions.

Read committed is the default level of SQL Server, while Repeatable read ensures data consistency better than read committed.

Features 2.1

Read committed blocks the update in other transactions, but does not block the SELECT statement.

Repeatable read not only blocks the update in other transactions, but also blocks the SELECT statement.

 

The similarities between Read committed and Repeatable read are that the update statements of other transactions are blocked.

The difference between Read committed and Repeatable read is that read committed does not block select statements of other transactions, but Repeatable read is blocked.

 

Note: Both read committed and Repeatable read are row-level locks. They only lock records related to themselves. After the transaction is committed, the blocked statement continues to be executed.

2.2 understanding 2.2.1 Read committed

The read committed transaction refers to the records that I select. Others can only view and cannot modify the records (only blocking the update of other transactions ).

 

The disadvantage of Read committed is that it cannot prevent the loss of reading inconsistencies and modifications.

Read inconsistency is because Read committed does not lock the read records. The loss of the modification is because other transactions can also read the records of the current transaction. Although the update of other transactions will be blocked, after the current transaction is committed, the update Statement of other transactions will continue to be executed and overwrite the results of the previous transaction, resulting in the loss of the last modification.

2.2.2 Repeatable read

The Repeatable read transaction refers to the records that I select, which cannot be viewed or modified by others (blocking select and update of other transactions ), this means that I can select data multiple times in the transaction without worrying about "Dirty reading"-this is what "repeatable reading" means.

 

Although Repeatable read solves the disadvantages of inconsistent reading and loss of modification of Read committed transactions, it also has disadvantages (although this disadvantage also exists in Read committed ):

Repeatable read does not block insert and delete, so the results of two select statements are different. In addition, Repeatable read consumes more resources than read committed.

3. Set the transaction isolation level in the Application

Read committed is the default isolation level of Microsoft SQL Server database engine.

 

When an isolation level is specified, all query and data operation language (DML) declarative locking behaviors in the SQL Server work phase will operate at this isolation level. This isolation level is valid until the end of the work phase or the isolation level is set to another level.

 

If the application must operate at different isolation levels, you can use the following methods to set the isolation level:

 

L execute the SET transaction isolation level statement.

 

L if the ADO. NET application uses the namespace managed by system. Data. sqlclient, you can use sqlconnection. begintransaction to specify the isolationlevel option.

 

L The application using ADO can set the autocommit isolation levels attribute.

 

L when starting a transaction, the application using ole db can set isolevel to the transaction isolation level to call itransactionlocal: starttransaction. When the isolation level is specified in the automatic approval mode, the application of ole db can set dbpropset_session attribute dbprop_sess_autocommitisolevels to the transaction isolation level.

 

L ODBC applications can use sqlsetconnectattr to set the SQL _copt_ss_txn_isolation attribute.

4. pessimistic lock

A pessimistic lock is used to assume that a concurrent update conflict occurs, so the lock mechanism is used no matter whether or not the conflict actually occurs.

 

A pessimistic lock locks read records to prevent other transactions from reading and updating these records. Other transactions will be blocked until the transaction ends.

 

The pessimistic lock is based on the transaction isolation function of the database and exclusively occupies resources to ensure consistency of read data and avoid loss of modification.

 

Pessimistic locks can use Repeatable read transactions, which fully meet the requirements of pessimistic locks.

5 optimistic lock

Optimistic locks do not lock anything, that is, they do not rely on the transaction mechanism of the database. Optimistic locks are completely at the application system level.

If an optimistic lock is used, the database must add the version field. Otherwise, only all fields can be compared. However, because the floating point type cannot be compared, no version field is actually unavailable.

6. deadlock

When two or more jobs have a resource lock, but other jobs attempt to lock the resource, and the work is permanently blocked from each other, a deadlock will occur. 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.

 

Except read uncommitted and snapshot, other types of transactions may have deadlocks.

Original article link: http://www.cnblogs.com/ego/articles/1456317.html