Reading notes SQL Business understanding

ACID properties of a transaction

atomicity Atomic Nature

Each transaction works as an atomic unit (that is, it can no longer be split), which means that all databases change transactions, either successfully or unsuccessfully.

SQL Server considers each DML or DDL command as a transaction. No commands are allowed to only partially succeed. For example, an UPDATE statement updates 500 rows, unless all 500 rows are updated, or there is any situation that prevents the command from being updated. SQL Server terminates the command update and rolls back the transaction.

Consistency consistency

Each transaction, regardless of success or failure, must have the same constraint state defined in the database, or it will be rolled back.

Let's say a transaction attempts to insert an invalid foreign key, which is seen by SQL Server as a violation of the constraint and generates an error prompt.

Isolation isolation

The execution of the transaction appears to be non-intrusive and the degree of isolation is based on the isolation level setting. For example, two transactions need to change the same data, one of which must wait for another to be completed before you can modify it.

SQL Server uses ' locks ' to achieve the purpose of transaction isolation. There are usually two kinds of locks shared locks shared lock used as read data Exclusive locks exclusive lock used as change data

Durability Persistence

The result of the transaction operation is saved (transaction log database transaction log). Each database change (data modification statement or DDL statement) first writes the original version of the data (updates and deletes) to the transaction log, and when the transaction commits and all consistency checks pass, the fact that the event is successfully committed is written to the transaction log. If the database was previously accidentally booted, the data is rolled back when it is started again.

Blocking Blocking

If two seesion apply for an exclusive lock on the same resource, when one generates an exclusive lock, the other must wait for the first one to be released (Commmit or roll back) to apply. That is, two sessions cannot write to the same resource at the same time, so that one write blocks the other. It's called blocking.

An exclusive lock prevents other transactions from reading the same resource, except that the same resource requests the exclusive lock to cause a blockage. Because exclusive locks and shared locks are incompatible.

Deadlock deadlocking

If there are two or more sessions blocking each other, this can cause a deadlock. When SQL Server detects it, it breaks one, and then returns error message 1205.

Session 1	Session 2
Use TSQL2012; BEGIN TRAN;	Use TSQL2012; BEGIN TRAN;
UPDATE HR. Employees SET region = N ' 10004 ' WHERE Empid = 1
	UPDATE production.suppliers SET Fax = N ' 555-1212 ' WHERE SupplierID = 1
UPDATE production.suppliers SET Fax = N ' 555-1212 ' WHERE SupplierID = 1
<blocked>	UPDATE HR. Employees SET phone = N ' 555-9999 ' WHERE Empid = 1
	<blocked>

After a deadlock occurs, one of the transactions is completed, and the other one is interrupted, and a 1205 error message is displayed

MSG 1205, Level 1
Transaction (Process ID) is deadlocked on lock resources with another Process and have been chosen as the deadlock Vict im. Rerun the transaction.

Finally, the successful execution of the transaction can be rolled back

IF @ @TRANCOUNT > 0 ROLLBACK

IF @ @TRANCOUNT > 0 ROLLBACK

@ @TRANCOUNT

Used to return the number of BEGIN TRANSACTION statements executed on the current connection.

If return 0 means that the current is not in a transaction, 1 means that within a transaction, greater than 1 is within a nested transaction.

Note that a transaction can contain only one rollback command, he rolls back the entire transaction, and then resets the @ @TRANCOUNT to 0

The actual examples are as follows

1. COMMIT TRAN

 UseTSQL2012;SELECT @ @TRANCOUNT;--= 0BEGIN TRAN; SELECT @ @TRANCOUNT;--= 1    BEGIN TRAN; SELECT @ @TRANCOUNT;--= 2        --Issue Data modification or DDL commands here    COMMIT    SELECT @ @TRANCOUNT;--= 1COMMIT TRAN;SELECT @ @TRANCOUNT;--= 0

2. ROLLBACK TRAN

 UseTSQL2012;SELECT @ @TRANCOUNT;--= 0BEGIN TRAN; SELECT @ @TRANCOUNT;--= 1    BEGIN TRAN; SELECT @ @TRANCOUNT;--= 2        --Issue Data modification or DDL command here    ROLLBACK;--rolls back the entire transaction at theSELECT @ @TRANCOUNT;--= 0

Transaction ISOLATION LEVEL

READ COMMITTED

This is the default isolation level, and only data changes are committed before they can be read. All SELECT statements attempt to acquire a shared lock, and an exclusive lock on another transaction session that modifies the data blocks the read COMMITTED session.

A query statement that joins with (NOLOCK) or with (readuncommitted) can be read directly

Note Now with (NOLOCK) deprecated, this option is not allowed in the update and DELETE statements in the new SQL version. To be with (readuncommitted)

SELECT LastName, FirstName  from  with (readuncommitted);

Example: Write blocking write

Session 1	Session 2
Use TSQL2012; BEGIN TRAN;	Use TSQL2012;
UPDATE HR. Employees SET PostalCode = N ' 10004 ' WHERE empid = 1;	UPDATE HR. Employees SET phone = N ' 555-9999 ' WHERE empid = 1;
<more work>	<blocked>
COMMIT TRAN;
	<results returned>

Write blocking read

Session 1	Session 2
Use TSQL2012; BEGIN TRAN;	Use TSQL2012;
UPDATE HR. Employees SET PostalCode = N ' 10005 ' WHERE Empid = 1	SELECT LastName, FirstName From HR. Employees
	<blocked>
COMMIT TRAN;
	<results returned>

READ uncommmited

This isolation level allows reader to read uncommitted data, which allows the SELECT statement not to request a shared lock and is not blocked by writer. However, the data being read may be rolled back to its original state, which results in dirty reads (reading dirty data)

Example

Session 1	Session 2
Use TSQL2012; BEGIN TRAN;	Use TSQL2012; SET TRANSACTION Isolation Level READ Uncommitted;
UPDATE HR. Employees SET region = N ' 1004 ' WHERE empid = 1;
	SELECT LastName, FirstName, Region From HR. Employees
	<results returned:region = 1004 for empid = 1>
ROLLBACK TRAN;
<region for empid = 1 rolled Original value>	SELECT LastName, FirstName, Region From HR. Employees;
	<results returned:region = original value for empid = 1>

Read COMMITTED SNAPSHOT This is actually not a new isolation level, which is an option for the read COMMITTED, which has the following characteristics:

Use tempdb to store the original version of the modified data.  This way, when reader reads the data, it reads the original version, does not require a shared lock, and is not blocked by writer. That is, read (RAW) commit data.

The READ COMMITTED SNAPSHOT option can be set for each database

RCSI (read COMMITTED SNAPSHOT) is not a separate isolation level, and the difference from read COMMITTED is simply to prevent writer from blocking reader.

RCSI is the default isolation level for Windows Azure SQL database

REPEATABLE Read Every read operation in the transaction is repeatable until the end of the transaction. All the data being read is locked by a shared lock, and the data cannot be changed by other updated or deleted transactions during the read. However, after the transaction reads are complete, new data rows may be added, resulting in Phantom reads (Phantom Read)

SNAPSHOT This isolation level also uses tempdb for versioning of empty rows, and transactions can only identify data modifications that were committed before they were started. Statements executed in the current transaction will not see data modifications made by other firms after the start of the current transaction. So there is no magic read (Phantom Read) , and the statement in the transaction gets the committed data snapshot that corresponds to the state of the data at the beginning of the transaction. SNAPSHOT isolation level does not require shared locks

SERIALIZABLE the strongest isolation. Other transactions cannot insert new rows into the table until a select transaction is complete.

Transaction Mode Transaction Modes

Autocommit

Once a single data modification or DDL is executed, the execution is automatically committed if it succeeds.

Implicit transaction

Implicit declaration, the first to open the implicit declaration, and then each statement execution will not be automatically committed or rolled back, need to handle manually. Examples are as follows:

 UseTSQL2012;SETImplicit_transactions on;SELECT @ @TRANCOUNT;--0SET Identity_insertProduction.Products on;--Issue DML or DDL command hereINSERT  intoproduction.products (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, discontinued)VALUES(101N'Test2:bad CategoryID',1,1,18.00,0);SELECT @ @TRANCOUNT;--1COMMIT TRAN;SET Identity_insertProduction.ProductsOFF;SETImplicit_transactionsOFF;--Remove the inserted rowDELETE  fromProduction.ProductsWHEREProductID= 101;--Note The row is deleted

Explicit Transaction Mode

An explicit declaration is to start a transaction with the Begin TRANSACTION or begin TRAN command. Examples are as follows:

 UseTSQL2012;SELECT @ @TRANCOUNT;--0BEGIN TRAN; SELECT @ @TRANCOUNT;--1    SET Identity_insertProduction.Products on; INSERT  intoproduction.products (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, discontinued)VALUES(101N'Test2:bad CategoryID',1,1,18.00,0); SELECT @ @TRANCOUNT;--1    SET Identity_insertProduction.ProductsOFF;COMMIT TRAN;

Reference documents

Sys.dm_tran_database_transactions

https://msdn.microsoft.com/en-us/library/ms186957 (v=sql.90). aspx

Write-ahead Transaction Log

http://msdn.microsoft.com/en-us/library/ms186259 (sql.105). aspx

@ @TRANCOUNT

Https://msdn.microsoft.com/zh-cn/library/ms187967.aspx

Detecting and ending Deadlocks

https://msdn.microsoft.com/en-us/library/ms178104 (sql.105). aspx

Transaction ISOLATION LEVEL

Https://msdn.microsoft.com/zh-cn/library/dn133175.aspx

Reading notes SQL Business understanding