sql server事务与锁

之前以为开启了事务，就可以将事务中使用到的所有的表，在各次事务中互相隔离开来。其实不是。事务是有可隔离级别的。

1、事务隔离级别

其中 sql server的事务隔离级别默认为read_committed。

2、脏读、不可重复读、幻读与加锁读
按隔离级别由低到高可排序为下：
（1）脏读即无隔离，当前事务可读到别的事务中未提交的修改值，允许外部update/insert/delete
（2）不可重复读，即在同一事务中不同阶段读取同一个条件下的结果集，得到不同的结果，允许外部insert
（3）幻读，即在同一事务中不同阶段读取同一个条件下的结果集，时间更早的那次结果集不全在时间更晚的结果集中，仿佛先前出现了幻觉一样。允许外部delete。
（不可重复读与幻读的区别与不同点也在此，前者强调是否在事务进行时，允许外部的insert，后者强调是否允许外部的delete）
（4）加锁读，即是否序列化，即事务无法并行，只能串行，一个接一个地执行。这里的加锁读稍有歧义，mssql的事务中select时默认会加共享锁（S锁）的，这里说加锁读应该特指加排他锁（X锁）
在mssql，可以通过工具-》 选项 查询执行 SQL SERVER 更改事务隔离级别

3、共享锁（S锁）、更新锁（U锁）、排他锁（X锁）
共享锁：在read_comitted的隔离级别下，事务中的select都默认给行加上S锁，此时其它事务不能update/delete该行数据，读取完毕即释放，不必等到事务结束 ，释放后其它事务可以修改或删除。一直可读取。
更新锁：当在事务中使用select xx from table with (updlock)…时，会给结果集的行加上U锁，读取完毕也不释放，直至事务结束，期间其它事务不能update/delete该行数据。因为U锁在同一时间对于同一行只能存在一个，所以它可以解决2个S锁竞争上升到X锁而造成的死锁问题，后面第4点会说。
排他锁：事物中update/delete操作时会加，此时其它事务无法select/update/delete。
S锁和U锁都可在发生修改update/delete时升级为X锁，

4、死锁的几种情况
死锁的第一种情况
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进 行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

死锁的第二种情况
用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操作，很容易就出现这种死锁的情况。

解决方法：
1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是 通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数 据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造 成脏数据被更新到数据库中。
3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统， 当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读 出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

死锁的第三种情况
如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。
解决方法：
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

总体上来说，产生内存溢出与锁表都是由于代码写的不好造成的，因此提高代码的质量是最根本的解决办法。有的人认为先把功能实现，有BUG时再在测试阶段进 行修正，这种想法是错误的。正如一件产品的质量是在生产制造的过程中决定的，而不是质量检测时决定的，软件的质量在设计与编码阶段就已经决定了，测试只是对软件质量的一个验证，因为测试不可能找出软件中所有的BUG。

参考链接：https://www.cnblogs.com/tian666/p/7852646.html