悲观锁和乐观锁

1        事务隔离

事务隔离是数据库提供的功能。

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

SET TRANSACTION ISOLATION LEVEL

    { READ UNCOMMITTED

    | READ COMMITTED

    | REPEATABLE READ

    | SNAPSHOT

    | SERIALIZABLE

    }

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

1.1         READ UNCOMMITTED

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

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

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

1.2         READ COMMITTED

Read Committed事务不能读取事务已修改，但未提交的记录。

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

1.3         REPEATABLE READ

Repeatable Read事务不能读取交易已修改，但未提交的记录，并且在事务完成之前，任何其它事务都不能修改目前事务已读取的记录。

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

1.4         SNAPSHOT

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

这相当于事务启动时，数据库为事务生成了一份专用“快照”。

在当前事务中看到不其它事务在当前事务启动之后所进行的数据修改。

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

1.5         SERIALIZABLE

Serializable事务会产生以下效果：

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

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

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

Serializable事务与select语句加holdlock的效果一样。

2        READ COMMITTED 和 REPEATABLE READ

Read Committed 和 Repeatable Read 是最常用的两种事务。

Read Committed 是 SQL Server的默认级别；而 Repeatable Read 比Read Committed 更能保证数据一致性。

2.1         特点

Read Committed会阻塞其它事务中的update，但不会阻塞select。

Repeatable Read不但会阻塞其它事务中的update，还会阻塞select。

 

Read Committed 和 Repeatable Read 的相同点是：都会阻塞其它事务的update语句。

Read Committed 和 Repeatable Read 的不同点是：Read Committed 不会阻塞其它事务的select语句，但Repeatable Read阻塞。

 

注意，Read Committed 和 Repeatable Read 都是行级锁，它们只会锁住与自己相关的记录。当事务提交之后，阻塞的语句就会继续执行。

2.2         理解

2.2.1     READ COMMITTED

Read Committed 事务的含义是我select出来的记录，别人只能看，不能改（只阻塞别的事务的update）。

 

Read Committed 的缺点是：无法防止读取不一致和修改丢失。

读取不一致是因为Read Committed 不锁住读取的记录；修改丢失是因为别的事务也能读取当前事务的记录，虽然会阻塞别的事务的update，但在当前事务提交之后，别的事务的update语句会继续执行，进而覆盖上一次事务的结果，导致上一次的修改丢失。

2.2.2     REPEATABLE READ

Repeatable Read 事务的含义是我select出来的记录，不允许别人看，也不允许别人改（阻塞别的事务select、update），这就意味着我可以在事务中多次select数据，而不用担心出现“脏读”——这就是“可重复读取”的意思。

 

Repeatable Read 虽然解决了Read Committed 事务的读取不一致和修改丢失的缺点，但它也有缺点（尽管这个缺点Read Committed 也有）：

Repeatable Read 不会阻塞insert和delete，所以会出现“幻读”——两次select的结果不一样。还有，Repeatable Read 占用的资源比Read Committed 大。

3        在应用程序中设置事务隔离级别

READ COMMITTED 是 Microsoft SQL Server Database Engine 的预设隔离等级。

已指定隔离等级时，在 SQL Server 工作阶段中，所有查询和数据操作语言 (DML) 陈述式的锁定行为都会在此隔离等级运作。此隔离等级会维持有效，直到工作阶段结束或隔离等级设为另一个等级为止。

如果应用程序必须在不同隔离等级操作，可以使用下列方法来设定隔离等级：

 

l     执行 SET TRANSACTION ISOLATION LEVEL Transact-SQL 陈述式。

 

l     如果 ADO.NET 应用程序使用 System.Data.SqlClient 管理的命名空间，可以使用 SqlConnection.BeginTransaction 方法来指定 IsolationLevel 选项。

l    使用 ADO 的应用程序可以设定 Autocommit Isolation Levels 属性。

l     当启动交易时，使用 OLE DB 的应用程序可以将 isoLevel 设为所要的交易隔离等级，以呼叫 ITransactionLocal::StartTransaction。当在自动认可模式中指定隔离等级时，使用 OLE DB 的应用程序可以将 DBPROPSET_SESSION 属性 DBPROP_SESS_AUTOCOMMITISOLEVELS 设为所要的交易隔离等级。

l     使用 ODBC 的应用程序可以使用 SQLSetConnectAttr 来定 SQL_COPT_SS_TXN_ISOLATION 属性。

4        悲观锁

     正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

5        乐观锁

     相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。
      而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据

6        死锁

当二或多个工作各自具有某个资源的锁定，但其它工作尝试要锁定此资源，而造成工作永久封锁彼此时，会发生死锁。例如：

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 而封闭了这个循环。

例如以下操作就会产生死锁，两个连接互相阻塞对方的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

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.

 

除了Read UnCommitted和Snapshot，其它类型的事务都可能产生死锁。

转载于:https://my.oschina.net/OQKuDOtsbYT2/blog/169938