读书笔记 SQL 事务理解

事务的ACID属性

Atomicity 原子性

每个事务作为原子单元工作（即不可以再拆分），也就是说所有数据库变动事务，要么成功要么不成功。

SQL Server把每个DML或者 DDL命令都当做一个事务。不允许任何命令只是部分成功。 比如一个UPDATE语句更新500行，除非500行全部更新，否则有任何情况阻止该命令更新。SQL Server会终止该命令更新，并且回滚事务。

Consistency 一致性

每个事务，不论成功或失败，数据库中定义的约束状态必须一致，否则会回滚。

比方说一个事务企图插入一个无效的外键，这会被SQL Server检测到违反约束，并且生成一个错误提示。

Isolation 隔离性

事务的执行看上去是互不干扰的，隔离的程度基于隔离级别设置。比方说，两个事务要更改同一个数据，其中之一必须等另外一个完成以后才能去修改。

SQL Server用‘锁’来达到事务的隔离的目的。 通常有两种锁 Shared locks 共享锁 用作读取数据  Exclusive locks 排它锁 用作变更数据

Durability 持久性

事务操作结果都会被保存下来（事务日志 database transaction log）。每个数据库变动（数据修改语句或者DDL语句）首先会把原始版本的数据（updates和deletes）写到事务日志，当事务提交，并且所有一致性检查都通过以后，事件成功提交的事实就会写入事务日志。如果数据库此之前意外当机，那么再次启动后，数据会回滚。

 

阻塞 Blocking

如果两个seesion在同样的资源上申请排它锁 ，当其中一个生成排它锁以后，另外一个必须等第一个释放后（commmit 或者 roll back）才能申请。也就是说同一时间内，两个会话无法写入同一个资源， 这样，一个写入阻塞了另外一个写入。这就叫阻塞

除了同一资源申请排它锁会造成堵塞之外，一个排他锁也会阻止其他事务读取同样的资源。因为排它锁和共享锁是不相容的。

 

死锁 Deadlocking

如果有两个或多个Session互相阻塞，这就会造成死锁。当SQL Server 检测到以后，会中断其中一个，然后返回错误信息 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>

发生死锁以后其中一个事务会完成，而另外一个会被中断，并且显示1205错误信息

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

最后可以把成功执行事务回滚

IF @@TRANCOUNT > 0 ROLLBACK IF @@TRANCOUNT > 0 ROLLBACK

 

@@TRANCOUNT

用来返回在当前连接上执行的 BEGIN TRANSACTION 语句的数目。

如果返回0则表示当前不在一个事务里面， 1表示在一个事务里面，大于1则表示在一个嵌套的事务里面。

image

注意一个事务只能包含一个ROLLBACK 命令，他会回滚整个事务，然后重置 @@TRANCOUNT 为0

image

实际例子如下

1. COMMIT TRAN

USE TSQL2012;
SELECT @@TRANCOUNT; -- = 0
BEGIN TRAN;
    SELECT @@TRANCOUNT; -- = 1
    BEGIN TRAN;
        SELECT @@TRANCOUNT; -- = 2
        -- Issue data modification or DDL commands here
    COMMIT
    SELECT @@TRANCOUNT; -- = 1
COMMIT TRAN;
SELECT @@TRANCOUNT; -- = 0

2. ROLLBACK TRAN

USE TSQL2012;
SELECT @@TRANCOUNT; -- = 0
BEGIN TRAN;
    SELECT @@TRANCOUNT; -- = 1
    BEGIN TRAN;
        SELECT @@TRANCOUNT; -- = 2
        -- Issue data modification or DDL command here
    ROLLBACK; -- rolls back the entire transaction at this point
SELECT @@TRANCOUNT; -- = 0

 

事务隔离级别

READ COMMITTED 

这个是默认隔离级别，只有数据更改被提交以后才能被读取们所有SELECT语句会企图取得一个共享锁，修改数据的另外一个事务会话的排他锁会阻塞READ COMMITTED 会话。

在查询语句加入 WITH (NOLOCK) 或 WITH (READUNCOMMITTED)可以直接读取

注意现在 WITH (NOLOCK) 不推荐使用，在新的SQL 版本中 Update和Delete 语句里面不允许用这个选项了。去而代之的是 WITH (READUNCOMMITTED)

SELECT lastname, firstname
FROM HR.Employees WITH (READUNCOMMITTED);

例子： 写入阻塞写入

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>

写入阻塞读取

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

这个隔离级别允许reader读取未提交的数据，这个设定使得SELECT 语句不用申请共享锁，不会被writer阻塞。然而被读取的数据在随后可能会回滚到原来的状态，这回导致脏读（reading dirty data)

例子

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 back to
original value>

SELECT lastname, firstname, region
FROM HR.Employees;
  <results returned: region = original value for empid = 1>

READ COMMITTED SNAPSHOT 这个实际上不是一个新的隔离级别，这是 READ COMMITTED的一个选项，该隔离级别有以下特性：

    ●使用tempdb来存储被修改数据的原始版本。 这样当reader 读取数据的时候读取的是原始版本 ，不需要共享锁，也不会被writer阻塞。  即读取 （原始）提交数据。

    ●READ COMMITTED SNAPSHOT 选项可以针对每个数据库设置

    ●RCSI（READ COMMITTED SNAPSHOT ） 不是独立的隔离级别，与READ COMMITTED的区别仅仅是防止writer阻塞reader。

    ●RCSI 是Windows Azure SQL Database的默认隔离级别

REPEATABLE READ   在事务结束之前，该事务中的每个读取操作都是可重复的。所有被读取的数据都会被共享锁锁定，读取过程中其他更新或删除的事务无法对这些数据做更改。 不过事务读取完成以后，可能会有新的数据行加入进来，导致幻读（phantom read）

SNAPSHOT 该隔离级别同样使用tempdb来空置行的版本控制，事务只能识别在其开始之前提交的数据修改。 在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。所以不存在幻读（phantom read） ，事务中的语句所获取的已提交数据快照对应于该数据在事务开始时的状态。SNAPSHOT 隔离级别不需要共享锁

SERIALIZABLE  最强的隔离。在一个SELECT事务完成之前，其他事务无法向表中插入新行。

 

事务模式 Transaction Modes

Autocommit

单个数据修改或者DDL 被执行以后，如果执行成功会自动提交。

Implicit transaction

隐式声明，先要开启隐式声明，然后每个语句执行以后不会自动提交或者回滚，需要手动处理。例子如下：

USE TSQL2012;
SET IMPLICIT_TRANSACTIONS ON;
SELECT @@TRANCOUNT; -- 0
SET IDENTITY_INSERT Production.Products ON;
-- Issue DML or DDL command here
INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
    VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
SELECT @@TRANCOUNT; -- 1
COMMIT TRAN;
SET IDENTITY_INSERT Production.Products OFF;
SET IMPLICIT_TRANSACTIONS OFF;
-- Remove the inserted row
DELETE FROM Production.Products WHERE productid = 101; -- Note the row is deleted

Explicit transaction mode

显式声明，就是用BEGIN TRANSACTION 或者 BEGIN TRAN 命令开始一个事务。例子如下：

USE TSQL2012;
SELECT @@TRANCOUNT; -- 0
BEGIN TRAN;
    SELECT @@TRANCOUNT; -- 1
    SET IDENTITY_INSERT Production.Products ON;
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
        VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
    SELECT @@TRANCOUNT; -- 1
    SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;

     

参考文档

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

事务隔离级别

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

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

https://msdn.microsoft.com/zh-cn/library/ms173763.aspx

posted @ 2015-03-15 17:09  Haseo  阅读(1474)  评论(1)  编辑  收藏  举报