Transactions in the database and lock __ Database

Transactions and locks in the database

ACID properties of a transaction

1,A (atomicity) atomicity

The transaction must be an atomic unit of work, or all of its data modifications, or none of them executed.

2,C (consistency) Consistency

When a transaction completes, you must keep all the data in a consistent state. In the related database, all rules must be applied to the modification of the transaction to preserve the integrity of all data. When a transaction ends, all internal data structures, such as a B-tree index or a two-way list, must be correct.

3, I (isolation) Isolation

Changes made by concurrent transactions must be isolated from any other concurrent transactions. The state that the data is in when the transaction recognizes the data, either when another concurrent transaction modifies its previous state, or when the second transaction modifies its state, the transaction does not recognize the intermediate state data.

4, D (durability) Durability

After the transaction is completed, its impact on the system is permanent. This modification is maintained even if a system failure occurs.

second, the complete transaction

Begin a transaction: Set the starting point of a transaction

Commit a transaction: commits a transaction so that the data submitted by the transaction becomes a persistent, immutable part.

ROLLBACK a transaction: Undo a transaction to make it the state before the transaction started.

SAVE a transaction: Create a label that is used as part of the rollback to revert to the initial state of the label.

syntax for transactions:

BEGIN tran[saction] [<transaction name>|< @transaction variable>][with MARK [' <description> ']][;]

COMMIT [Tran[saction] [<transaction name>|< @transaction variable>]][;]

ROLLBACK tran[saction] [<transaction name>|<save point name>|< @transaction variable>|<@ SavePoint variable>][;]

SAVE Tran[saction] [<save point name>| < @savepoint variable>][;]

example of a complete transaction:

Use AdventureWorks2008; --We ' re making our own table-what DB doesn ' t matter--create table to work with CREATE TABLE Mytrantest (OrderID INT
PRIMARY KEY IDENTITY);
--Start The transaction BEGIN TRAN Transtart;
--Insert our piece of data using default values. --Consider this record No1.
It is also the 1st record, that stays, after all the rollbacks are done.
INSERT into Mytrantest DEFAULT VALUES;
--Create a "Bookmark" to come back to later if need is SAVE TRAN firstpoint;
--Insert Some more default data (this one'll disappear-after the rollback).
--Consider this record No2.
INSERT into Mytrantest DEFAULT VALUES; --Roll back to the "the" "SavePoint. Anything up to, point would still be part of the transaction.
Anything--Beyond is now toast.
ROLLBACK TRAN Firstpoint;
INSERT into Mytrantest DEFAULT VALUES;
--Commit the transaction commit TRAN Transtart;
--what records were finally committed.
SELECT top 2 OrderID from Mytrantest order by OrderID DESC; --Clean up after ourselves DROP TABLE mytrantest;
 

Three, lock

Locking is a mechanism used by the Microsoft SQL Server database engine to synchronize multiple users ' access to the same data block at the same time.

Before a transaction acquires a dependency on the current state of a block of data, such as by reading or modifying data, it must protect itself from other transactions that modify the same data. The transaction achieves this by requesting a lock on the block of data. Locks are available in a variety of modes, such as shared or exclusive. The lock mode defines the level of dependency that a transaction has on the data. If a transaction has acquired a lock on a particular data, the other transaction cannot obtain a lock that conflicts with the lock mode. If the lock mode of a transaction request conflicts with a lock that has been granted the same data, the database engine instance pauses the transaction request until the first lock is released.

Four major conflict issues

1. Dirty reading

The data that a transaction reads is data that is being processed by another transaction. Another transaction may be rolled back, causing the data read by the first transaction to be incorrect.

2. Non-repeatable reading

Data is read two times in one transaction, but another transaction has changed the data involved in the first transaction, causing the first transaction to read the old data.

3. Phantom reading

Phantom reading refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table that involves all the rows of data in the table. At the same time, the second transaction modifies the data in this table, which is inserting a row of new data into the table. So, it's going to happen later. The user of the first transaction discovers that there are no modified data rows in the table, as if there were hallucinations.

4, update lost

Multiple transactions read a single data at the same time, one transaction successfully processed the data, and another transaction was written back to the original value, causing the first transaction update to be lost.

Lock mode:

1. Shared lock

Shared locks (S locks) allow concurrent transactions to read (SELECT) resources under closed concurrency control. For more information, see Types of concurrency control. When a shared lock (S lock) exists on a resource, no other transaction can modify the data. Once the read operation completes, the shared (s) lock on the resource is released immediately, unless the transaction isolation level is set to repeatable read or higher, or a shared lock (S lock) is reserved with a lock hint during the transaction duration.

2. Update lock (U lock)

Updates a cross between a shared lock and an exclusive lock. Updating a lock means that when an update is made, a shared lock may be converted to an exclusive lock after it has been scanned for data that meets the criteria.

There are two steps in this:

1) When the scan gets the where condition. This section is an update query, and this is an update lock.

2 If write update will be performed. The lock is upgraded to an exclusive lock at this time. Otherwise, the lock is converted to a shared lock.

Updating locks can prevent common deadlocks.

3. Exclusive lock

Exclusive locks (X locks) prevent concurrent transactions from accessing resources. Exclusive locks are not compatible with any other locks. When an exclusive (x) lock is used, no other transaction can modify the data, and the read operation is only performed when the NOLOCK hint is used or the Read isolation level is not committed.

Transaction ISOLATION Level

SQL Server sets transaction isolation levels through the SET TRANSACTION isolation LEVEL statement:

SET TRANSACTION Isolation Level

{READ UNCOMMITTED

| READ committed

| Repeatable READ

| SNAPSHOT

| SERIALIZABLE

}

[ ; ]

The Read committed is a preset isolation level for SQL Server and Oracle.

1, READ uncommitted

The READ UNCOMMITTED transaction can read records that have been modified by the transaction but have not been committed.

READ UNCOMMITTED transaction produces dirty reads (Dirty read).

The Read uncommitted transaction, like the effect of a SELECT statement plus nolock, is the least restrictive of all isolation levels.

2, READ committed

Once the statement that created the shared lock completes, the lock is released.

The Read committed is the default isolation level for SQL Server.

Read Committed can only prevent dirty reads.

--Create TABLE 
TB (id int,val int) 
Insert TB VALUES (1,10) 
Insert TB VALUES (2,20) 
  
and then in connection 1, execute: 
SET TRANSACTION Isolation Level READ committed
BEGIN TRANSACTION
    SELECT * from TB;  -After this SELECT is over, the shared lock 
      
    WAITFOR DELAY ' 00:00:05 ' is released  --Simulate transaction processing, wait 5 seconds for 
      
    SELECT * from TB;   --Again Select TB table ROLLBACK-rollback  transaction 
  
in connection 2, execute 
UPDATE tb SET
    val = val + 
WHERE id = 2; 
  
-------- 
back to connection 1. As you can see, the results of the two-time select are different. 
Because at the default read Committed isolation level, the Select is finished. The shared lock will be released immediately. 

3, Repeatable READ

Repeatable read transactions do not produce dirty reads, and no other transaction can modify records that are currently read by the transaction until the transaction is completed.

Other transactions can still insert new records, but must conform to the search criteria for the current transaction-which means that the current transaction requery the record produces a Phantom read.

4, SERIALIZABLE

Serializable can prevent all consistency issues except the loss of updates, namely:

1. Statements cannot read records that have been modified but not committed by other transactions.

2. Other transactions cannot modify records that are currently read by the transaction until the current transaction is completed.

3. Before the current transaction completes, a new record inserted by another transaction, whose key value cannot be in the range of index keys read by any statement of the current transactions.

5, SNAPSHOT

Snapshot records that are read by any statement in a transaction are data at the start of a transaction.

This equates to a dedicated "snapshot" of the transaction when the transaction starts. See no other transactions in the current transaction after the current transaction has started.

Snapshot transactions require a lock when the record is not read, 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 read data.

Four, pessimistic lock and optimistic lock

1. Pessimistic lock

Pessimistic locking refers to the assumption that concurrent update conflicts occur, so that the lock mechanism is used regardless of whether the conflict actually occurs.
Pessimistic locks complete the following functions: Lock the Read records and prevent other transactions from reading and updating the records. Other transactions will remain blocked until the end of the transaction.
Pessimistic lock is the use of the transaction isolation function of the database on the basis of exclusive consumption of resources, so as to ensure the consistency of reading data, to avoid the loss of modification.

Pessimistic lock can use repeatable read transaction, it completely satisfies the request of pessimistic lock.

2. Optimistic lock

An optimistic lock does not lock anything, that is, it does not depend on the transaction mechanism of the database, and the optimistic lock is entirely an application-level thing.

If you use optimistic locks, the database must have a version field, otherwise you can only compare all the fields, but since floating-point types cannot be compared, it is not feasible to actually have no version fields.

3, Dead lock

Deadlocks occur when two or more jobs each have a lock on a resource, but other efforts attempt to lock the resource and cause the work to block each other permanently. For example:
1. Transaction a obtains the shared lock of data column 1.
2. Transaction B obtains the shared lock of data column 2.
3. Transaction a now requires exclusive locking of data column 2, but will be blocked until transaction B completes and the shared lock on data column 2 is released.
4. Transaction B now requires exclusive locking of data column 1, but will be blocked until transaction a completes and releases a shared lock on data column 1.
Transaction A cannot be completed until transaction B is completed, but transaction B is blocked by transaction a. This condition is also known as "cyclic dependencies" (cyclic Dependency). Transaction A is dependent on transaction B, and transaction B closes the loop because it is dependent on transaction a.

SQL Server automatically kills one of the transactions when it encounters a deadlock, while another transaction ends gracefully (commits or rolls back).

SQL Server returns an error code of 1205 for the connection that was killed, and the exception prompt is:

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

For example, the following operation produces a deadlock, and two connections block each other's update.

Connection 1: 
BEGIN tran 
  
SELECT * FROM Customers  
  
Update customers set CompanyName = CompanyName waitfor 
  
   
' 00: 00:05 '
  
   
select * from Employees 
-because Employees is locked by 2 connected, this blocks. 
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 Customers  
- Because the customers is connected 1 locked, so this will block. 
Update Customers Set CompanyName = CompanyName 
Commit Tran

4. How to avoid deadlock

(1). Access objects in the same order. (Note: avoid loops)
(2). Avoid user interaction in a transaction. (Note: Less time to hold resources, less lock competition)
(3). Keep the transaction short and in a batch. (Note: Same (2), less time to hold resources)
(4). Use a lower isolation level. (Note: Use a lower isolation level (for example, committed reading) to hold a shared lock for a shorter time than using a higher isolation level (for example, serializable), reducing lock contention)
(5). Use row versioning for a transaction that supports snapshot transaction isolation and specifies the Read_committed isolation level, with row versioning, to minimize the likelihood of deadlock between read and write operations:
SET allow_snapshot_isolation on--transactions can specify SNAPSHOT transaction isolation levels;
Set Read_committed_snapshot on--Specifies that transactions at the Read_committed isolation level will use row versioning instead of locking. By default (this option is not turned on, without the WITH NOLOCK hint), the SELECT statement adds an S lock (shared lock) to the requested resource, and when this option is turned on, the select does not add s lock to the requested resource.
Note: When you set the Read_committed_snapshot option, only connections that perform the ALTER database command are allowed in the database. There must be no other open connections in the database until ALTER database completes. The database does not have to be in Single-user mode.
(6). Use a bound connection. (Note: A binding session facilitates the coordination of operations between multiple sessions on the same server.) A binding session allows one or more sessions to share the same transaction and lock (but each reply retains its own transaction isolation level) and can use the same data without a lock conflict. You can create a binding session from multiple sessions within the same application, or you can create a binding session from multiple applications that contain different sessions. After a transaction is opened in a session (begin TRAN), the exec sp_getbindtoken @Token out is invoked, the Token is obtained, and then a second session is passed and the exec sp_bindsession @Token is executed to bind ( A binding connection is shown in the final example.

v.. NET to start a transaction using the specified isolation level

The BeginTransaction function has multiple overloads, one of which can specify the isolation level of the transaction

BeginTransaction (IsolationLevel): Starts a database transaction at the specified isolation level.

Note: After a transaction commits or rolls back, the isolation level of the transaction is saved for all subsequent commands in autocommit mode (SQL Server default settings). This will produce unexpected results, such as the repeatable read isolation level persists and prevents other users from using a row. To reset the isolation level to the default (read operation), execute Transact-SQL SET TRANSACTION ISOLATION LEVEL read statement or call sqlconnection.begintransaction followed by Sqltransaction.commit.