数据库中的事务和锁

数据库中的事务和锁

一、事务的ACID特性

1、 A (Atomicity)  原子性

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

2、 C (Consistency)一致性

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B树索引或双向链表）都必须是正确的。

3、I (Isolation)   隔离性 

并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。

4、D (Durability)  持久性

事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

 

二、完整的事务

BEGIN a transaction: 设置事务的起始点

COMMIT a transaction: 提交事务,使事务提交的数据成为持久,不可更改的部分.

ROLLBACK a transaction:撤消一个事务,使之成为事务开始前的状态.

SAVE a transaction:建立一个标签,做为部分回滚时使用,使之恢复到标签初的状态.

 

事务的语法：

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>][;]

 

事务完整的例子：

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 first 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 be
SAVE TRAN FirstPoint;
-- Insert some more default data (this one will disappear
-- after the rollback).
-- Consider this record No2.
INSERT INTO MyTranTest
DEFAULT VALUES;
-- Roll back to the first savepoint. Anything up to that
-- point will 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;
-- See what records were finally committed.
SELECT TOP 2 OrderID
FROM MyTranTest
ORDER BY OrderID DESC;
-- Clean up after ourselves
DROP TABLE MyTranTest;

 

 

三、锁

锁定是 Microsoft SQL Server数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

在事务获取数据块当前状态的依赖关系（比如通过读取或修改数据）之前，它必须保护自己不受其他事务对同一数据进行修改的影响。事务通过请求锁定数据块来达到此目的。锁有多种模式，如共享或独占。锁模式定义了事务对数据所拥有的依赖关系级别。如果某个事务已获得特定数据的锁，则其他事务不能获得会与该锁模式发生冲突的锁。如果事务请求的锁模式与已授予同一数据的锁发生冲突，则数据库引擎实例将暂停事务请求直到第一个锁释放。

 

四大冲突问题

1、脏读

某个事务读取的数据是另一个事务正在处理的数据。而另一个事务可能会回滚，造成第一个事务读取的数据是错误的。

 

2、不可重复读

在一个事务里两次读入数据，但另一个事务已经更改了第一个事务涉及到的数据，造成第一个事务读入旧数据。

 

3、幻读

幻读是指当事务不是独立执行时发生的一种现象。例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

 

4、更新丢失

多个事务同时读取某一数据，一个事务成功处理好了数据，被另一个事务写回原值，造成第一个事务更新丢失。

 

锁模式：

1、共享锁

共享锁（S 锁）允许并发事务在封闭式并发控制下读取 (SELECT)资源。有关详细信息，请参阅 并发控制的类型。资源上存在共享锁（S锁）时，任何其他事务都不能修改数据。读取操作一完成，就立即释放资源上的共享锁（S锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S锁）。

2、更新锁（U锁）

更新锁在共享锁和排他锁的杂交。更新锁意味着在做一个更新时，一个共享锁在扫描完成符合条件的数据后可能会转化成排他锁。

 

这里面有两个步骤：

1) 扫描获取Where条件时。这部分是一个更新查询，此时是一个更新锁。

2) 如果将执行写入更新。此时该锁升级到排他锁。否则，该锁转变成共享锁。

 

更新锁可以防止常见的死锁。

 

3、排他锁

排他锁（X 锁）可以防止并发事务对资源进行访问。排他锁不与其他任何锁兼容。使用排他锁（X锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK提示或未提交读隔离级别时才会进行读取操作。

 

事务隔离级别 

SQL Server通过SET TRANSACTION ISOLATION LEVEL语句设置事务隔离级别： 

SET TRANSACTION ISOLATION LEVEL

    { READ UNCOMMITTED

    | READ COMMITTED

    | REPEATABLE READ

    | SNAPSHOT

    | SERIALIZABLE

    }

[ ; ]

 

Read Committed是SQL Server和Oracle的预设隔离等级。

 

 

1、READ UNCOMMITTED

Read UnCommitted事务可以读取事务已修改，但未提交的的记录。

Read UnCommitted事务会产生脏读（Dirty Read）。

Read UnCommitted事务与select语句加nolock的效果一样，它是所有隔离级别中限制最少的。

 

2、READ COMMITTED

一旦创建共享锁的语句执行完成，该锁顶便释放。

Read Committed是SQL Server的预设隔离等级。

Read Committed只可以防止脏读。

--先创建表: 
CREATE TABLE tb(id int,val int) 
INSERT tb VALUES(1,10) 
INSERT tb VALUES(2,20) 
  
然后在连接1中,执行: 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
    SELECT * FROM tb;  --这个SELECT结束后,就会释放掉共享锁 
      
    WAITFOR DELAY '00:00:05'  --模拟事务处理,等待5秒 
      
    SELECT * FROM tb;   --再次SELECT tb表 
ROLLBACK  --回滚事务 
  
在连接2中,执行 
UPDATE tb SET
    val = val + 10 
WHERE id = 2; 
  
-------- 
回到连接1中.可以看到.两次SELECT的结果是不同的. 
因为在默认的READ COMMITTED隔离级别下,SELECT完了.就会马上释放掉共享锁. 

 

 

3、 REPEATABLE READ

REPEATABLE READ事务不会产生脏读，并且在事务完成之前，任何其它事务都不能修改目前事务已读取的记录。

其它事务仍可以插入新记录，但必须符合当前事务的搜索条件——这意味着当前事务重新查询记录时，会产生幻读（Phantom Read）。

 

4、 SERIALIZABLE

SERIALIZABLE可以防止除更新丢失外所有的一致性问题,即:

 

1.语句无法读取其它事务已修改但未提交的记录。

2.在当前事务完成之前，其它事务不能修改目前事务已读取的记录。

3.在当前事务完成之前，其它事务所插入的新记录，其索引键值不能在当前事务的任何语句所读取的索引键范围中。

 

5、 SNAPSHOT

Snapshot事务中任何语句所读取的记录，都是事务启动时的数据。

这相当于事务启动时，数据库为事务生成了一份专用“快照”。在当前事务中看到不其它事务在当前事务启动之后所进行的数据修改。

Snapshot事务不会读取记录时要求锁定，读取记录的Snapshot事务不会锁住其它事务写入记录，写入记录的事务也不会锁住Snapshot事务读取数据。

 

四、悲观锁和乐观锁

1、悲观锁

悲观锁是指假设并发更新冲突会发生，所以不管冲突是否真的发生，都会使用锁机制。
悲观锁会完成以下功能：锁住读取的记录，防止其它事务读取和更新这些记录。其它事务会一直阻塞，直到这个事务结束.
悲观锁是在使用了数据库的事务隔离功能的基础上，独享占用的资源，以此保证读取数据一致性，避免修改丢失。

悲观锁可以使用Repeatable Read事务，它完全满足悲观锁的要求。

2、乐观锁

乐观锁不会锁住任何东西，也就是说，它不依赖数据库的事务机制，乐观锁完全是应用系统层面的东西。

如果使用乐观锁，那么数据库就必须加版本字段，否则就只能比较所有字段，但因为浮点类型不能比较，所以实际上没有版本字段是不可行的。

 

3、死锁

当二或多个工作各自具有某个资源的锁定，但其它工作尝试要锁定此资源，而造成工作永久封锁彼此时，会发生死锁。例如：
1.           事务 A取得数据列 1 的共享锁定。
2.           事务B取得数据列 2 的共享锁定。
3.           事务A现在要求数据列 2 的独占锁定，但会被封锁直到事务B 完成并释出对数据列 2 的共享锁定为止。
4.           事务B现在要求数据列 1 的独占锁定，但会被封锁直到事务A 完成并释出对数据列 1 的共享锁定为止。
等到事务B 完成后，事务A 才能完成，但事务B被事务A 封锁了。这个状况也称为「循环相依性」(Cyclic Dependency)。事务A相依于事务B，并且事务B也因为相依于事务A 而封闭了这个循环。

 

SQL Server遇到死锁时会自动杀死其中一个事务，而另一个事务会正常结束（提交或回滚）。

SQL Server对杀死的连接返回错误代码是1205，异常提示是：

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.

 

例如以下操作就会产生死锁，两个连接互相阻塞对方的update。

连接1： 
begin tran 
  
select * from customers  
  
update customers set CompanyName = CompanyName 
  
   
waitfor delay '00:00:05'
  
   
select * from Employees 
–因为Employees被连接2锁住了，所以这里会阻塞。 
update Employees set LastName = LastName  
commit tran 
   
连接2： 
begin tran 
  
select * from Employees 
  
update Employees set LastName = LastName 
  
   
waitfor delay '00:00:05'
  
   
select * from customers  
--因为customers被连接1锁住了，所以这里会阻塞。 
update customers set CompanyName = CompanyName 
commit tran

 

 

4、如何避免死锁

(1).按同一顺序访问对象。(注：避免出现循环)
(2).避免事务中的用户交互。(注：减少持有资源的时间，较少锁竞争)
(3).保持事务简短并处于一个批处理中。(注：同(2)，减少持有资源的时间)
(4).使用较低的隔离级别。(注：使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短，减少锁竞争)
(5).使用基于行版本控制的隔离级别：2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制，可以将读与写操作之间发生的死锁几率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定 SNAPSHOT事务隔离级别;
SET READ_COMMITTED_SNAPSHOT ON  --指定 READ_COMMITTED隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项，没有加with nolock提示)，SELECT语句会对请求的资源加S锁(共享锁)；而开启了此选项后，SELECT不会对请求的资源加S锁。
注意：设置 READ_COMMITTED_SNAPSHOT选项时，数据库中只允许存在执行 ALTER DATABASE命令的连接。在 ALTER DATABASE完成之前，数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。
(6).使用绑定连接。(注：绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别)，并可以使用同一数据，而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin tran)后，调用exec sp_getbindtoken @Token out;来取得Token，然后传入另一个会话并执行EXEC sp_bindsession @Token来进行绑定(最后的示例中演示了绑定连接)。

 

五、.NET中使用指定的隔离级别开始一个事务

BeginTransaction函数有多个重载，其中一个可以指定事务的隔离级别

BeginTransaction(IsolationLevel)：以指定的隔离级别启动数据库事务。

 

注意：在事务提交或回滚后，该事务的隔离级别为自动提交模式的所有后续命令保存 ( SQL Server默认设置) 中。这样将产生意外结果，例如 REPEATABLE READ隔离级别持续并阻止其他用户使用某一行。若要重置隔离级别为默认值 (读取操作)，执行 Transact-SQL 设置事务隔离级别读作的语句或调用 SqlConnection.BeginTransaction 后面紧跟 SqlTransaction.Commit。