sqlserver 事务和锁

斜体部分是本人注:

结合SQL的 

WAITFOR DELAY '00:00:05'

可以自己实验

锁和事务隔离级别是两个不同的概念, 锁是更加具体的手段去实现了各个不同的事务级别.

 

事务

事务隔离级别:

1、Read Uncommitted（读取其他事务未提交内容）

在该隔离级别中，所有事务可以看到其他未提交事务的执行结果。本级别隔离很少用于实际应用，比其他隔离级别性能也好不了多少。读取未提交的数据成为“脏读”（Dirty Read）。

2、Read Committed（只读取其他事务已经提交内容, 解决脏读问题）

这是大多数数据库默认的隔离级别，但是，不是MySQL的默认隔离级别。它满足了隔离简单的定义：一个事务只能看到已提交事务所做的改变。这种隔离会出现“不可重复读”的现象（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit（包括update和delete），导致目标数据被修改，所以同一select可能返回不同结果。

3、Repeatable Read（可重读, 解决脏读问题, 解决不可重复读）

这是MySQL默认的隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行（目标数据行不会被修改）。不过，会出现“幻读”的现象。幻读是由insert引起的，简单来说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

4、Serializable（可串行化,解决脏读问题, 解决不可重复读, 解决幻读问题）

这是最高的事务隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简而言之，它是在每个读的数据行上，加了共享锁，在这个级别，可能导致大量的超时现象和锁竞争。

 

脏读: 读了其他事务未commit的数据, 然后那个事务rollback

不可重复读: 在事务执行过程中, 读了某数据, 然后另一个事务改了这个数据(并且commit了), 再读, 发现了数据改变了

幻读: 在事务执行过程中, 读了个区间的数据的行数(或者其他聚合值), 然后另一个事务改了这个数据(并且commit了), 再读行数, 发现了行数改变了

 

不可重复读和幻读的区别是: 不可重复读指某个数据本身, 而幻读指某个区间的数据的行数之类的聚合值

作者：okbrochure
链接：https://www.jianshu.com/p/faba8a3a6d67
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

 

锁

NOLOCK 可能把没有提交事务的数据也显示出来．

READPAST 会把被锁(一般指排他锁)住的行不显示出来 .

 

共享锁不用加任何关键词，默认就有，共享锁就是大家都能读，谁也不能改

 

更新锁：select with updlock，和共享锁一样：大家都能读，谁也不能改

 

但共享锁和更新锁的重要区别是:

共享锁在其语句一结束后立刻失效(除非用holdlock)，而更新锁的生命周期则与事务一致。

共享锁可以叠加共享锁. 但当对象上有其他锁存在时，无法对其加更新锁。

也就是说先有更新锁再加共享锁是可以的，但是先有共享锁就不能追加更新锁了。

 

更新锁和排他锁的区别：排他锁一般只在update，insert，delete这样的语句中才会使用，而更锁可以通过select来对select的数据启用。排他锁时，其他线程读都不能读。更新锁在具体update，insert，delete会升级成排它锁.

 

加了更新锁的数据还能继续加共享锁的，更新锁只在事务执行update或delete时升级为排他锁。

http://blog.csdn.net/u010523770/article/details/53208817

 

 

死锁：两边都有了共享锁，现在两边都想更新同一行数据，都等着对面释放共享锁，然后拿到排他锁来更新，于是死锁。

 

可以通过Profile跟踪的Lock:Acquired (加锁)事件来看加锁情况。

 

 

如何避免死锁，最小化锁竞争

1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务，事务持有锁的时间越短，锁竞争发生的机会就越少；将不是事务所管理的工作单元锁必需的命令移出事务。；

2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；

3 优化程序，检查并避免死锁现象出现；

4 .对所有的脚本和SP都要仔细测试，在正是版本之前。

5 所有的SP都要有错误处理（通过@error）

 

 if @@ERROR<>0 
begin
   rollback transaction
end

6 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁

 

7 将组成事务的语句作为一个的单独的批命令处理，以消除 BEGIN TRAN 和 COMMIT  TRAN 语句之间的网络延迟造成的不必要的延迟。

8 考虑完全地使用存储过程编写事务代码。典型地，存储过程比批命令运行更快。

9 在游标中尽可早地Commit更新。因为游标处理比面向集合的处理慢得多，因此导致锁被持有的时间更久。

10 使用每个进程所需的最低级别的锁隔离。比如说，如果脏读是可接受的并且不要求结果必须精确，那么可以考虑使用事务隔离级别0（Read Uncommitted）,仅在绝对必要时才使用Repeatable Read or Serializable隔离级别。

11 在 BEGIN TRAN 和 COMMIT TRAN 语句之间，绝不允许用户交互，因为这样做可能锁被持有无限期的时间。