SQL SERVER 锁与锁的级别学习

锁的种类

1共享锁s 2排他锁x

del insert update 2排他锁x一定会有的

select 1共享锁s或者没有锁

SQL SERVER 隔离的级别

表名 + WITH (READUNCOMMITTED) 没有锁随便读取，但是会出现脏读（即事务中未提交的更新数据） 幻读（事务1读取记录时事务2增加了记录并提交，事务1再次读取时可以看到事务2新增的记录 这挺好的，有时候不行）

优点：不怕死锁 读取速度爽 缺点：脏读，幻读

 

表名 + WITH (READCOMMITTED)(sql默认级别) 在读取时加入共享锁（注意读取时，也就是说执行完了后事务内是没有锁的，只是在检索那瞬间），没有脏读，但有幻读

优点：没有脏读 缺点：幻读，或死锁

注意：不同事务中因为他事务del insert update有x锁，所以当要在本事务读取时，由于取不到s锁会出现等待（这个就是有时候死锁的根源），在他事务完成后，可以检索到他事务完成后的变化结果（这个就是所谓的幻读吧，其实挺好的）。但是如果在同一次事务中，由于READCOMMITTED级别只在检索中持有共享，完成检索后再进行del insert update后再次进行，相同检索会出现数据不一致（其实这有时候挺好的）。

死锁注意：这个会出现死锁的的所以请注意在tran中的使用（大概太基础不太会出现吧）。

 

ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT ON;在读取时没有锁，没有脏读，但有幻读。 这个功能比较难以理解。

检索时，取得更新版的上一版本的内容(注意是在检索时不是在事务开始)。并且不加共享锁。

优点：没有脏读，无死锁，不等待 缺点：幻读，del update成本追加

原理如下，st是执行顺序

事务1                       事务2

st2: sel table 1         st1: upd table 1 set a = 20

result a = 0(原值)     st3: commit

st4: sel table 1     

result a = 20(更新后值) 

st5:commit

总结：果然是非常奇怪啊。不过没有锁而且没有脏读，还是不错的。由于保留了版本值,更新这种性能成本会增加。

 

表名 + WITH (REPEATABLEREAD) 在整个事务中加入共享锁，可以对范围行更新进行限制，但是没有办法阻止追加操作，没有脏读，但有幻读

如：

事务1                                                                                       事务2

BEGIN TRAN;                                                                            UPDATE Sales.OrderDetails
 SELECT ORDERID, discount                                                       SET DISCOUNT=0.00
 FROM Sales.OrderDetails WITH(REPEATABLEREAD)                     WHERE ORDERID=10249;
 WHERE ORDERID=10249; 

如果事务1不结束的话，其他事务对于discount的更新是不允许的。但是请注意如果检索时没有追加discount项目的话，discount的更新将被允许。也就是说这个可以到列范围。

如果在事务1下加入这样的更新是可以的。因为共享锁在事务内部可以被取得。

 UPDATE Sales.OrderDetails
 SET DISCOUNT+=0.05
 WHERE ORDERID=10249;

注意：因为是事务级的共享锁。非常容易造成他事务的更新阻塞。如果事务时间过长的话，这种情况会很明显。所以在使用时请注意。从事务级的锁开始，死锁率就上升了。

事务中使用的有点:可以保证本逻辑中，正在使用的数据不会被他人更新。但是如果有多个同时执行时，会出现死锁情况。如下：

BEGIN TRAN;  

SELECT ORDERID, discount  FROM Sales.OrderDetails WITH(REPEATABLEREAD)  WHERE ORDERID=10249;〈-----如果这个事务同时被执行。

UPDATE Sales.OrderDetails  SET DISCOUNT+=0.05  WHERE ORDERID=10249; 〈-------这里就会因为需要等待，另一个事务的结束而无法结束本事务。造成死锁。

COMMIT TRAN

表名 + WITH (SERIALIZABLE)在整个事务中加入共享锁，可以对范围行更新进行限制，可以阻止追加操作，没有脏读，没有幻读

最严厉的锁了，大致和REPEATABLEREAD相同，就是追加了插入限制。

如

表结构

事务1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　事务2

BEGIN TRAN;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　INSERT INTO [TSQL2012].[Sales].[OrderDetails] VALUES(10249, 11, 14, 12, 0)
 SELECT ORDERID, discount
 FROM Sales.OrderDetails WITH(SERIALIZABLE)
 WHERE ORDERID=10249;

事务1运行时，事务2的插入将进行等待。

 

ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION ON;〈--允许SNAPSHOT

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;和READ_COMMITTED_SNAPSHOT一样都是加入了版本控制，所以更新，删除成本上升（因为事务结束前都要留版本）。

和READ_COMMITTED_SNAPSHOT相比，多了更新冲突检测。所以没有幻读。SNAPSHOT是事务级的版本控制（注意不是共享锁），所以没有死锁。但是可以检测更新冲突来控制数据一致性。

例子    st是顺序标号。

事务1                                                      事务2

st1: sel a from table 1〈 检索出现有版本       st2: upd table 1 set a = 2〈-----先进行更新

result a = 0(原值)                                    st3: commit

st4: upd table 1 set a = 2 <----再要更新是出现版本冲突出错

st5:commit

メッセージ 3960、レベル 16、状態 2、行 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'Production.Products' directly or indirectly in database 'TSQL2012' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

也就是说snapshot可以检测本事务的更新前，是否会有它事务先一步进行更新。

最后

当然还可以使用 SET TRANSACTION ISOLATION LEVEL XXX;来设定级别。这样设好后用一次连接的检索都回变成该级别。

 

终于结束了。

<<=====================================下面是死锁检测===========================================>>

检查锁定的方法

SELECT
 request_session_id,
 resource_type,
 resource_database_id,
 resource_description,
 resource_associated_entity_id,
 request_mode,
 request_status
FROM sys.dm_tran_locks

结果下面这个有wait的就是了

 

其他的检索方法

sys.dm_exec_connections 检索连接

sys.dm_exec_sessions 检索机器名登录用户和session

SELECT
blocking_session_id,
command,
sql_handle,
database_id,
wait_type,
wait_time,
wait_resource
FROM
sys.dm_exec_requests
WHERE blocking_session_id > 0

检索等待的语句需求的资源

SELECT text
FROM
SYS.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(MOST_RECENT_SQL_HANDLE) as st
WHERE session_id IN (51,52)<---这个是sys.dm_tran_locks检索出的x s锁的session id

结果 sql语句出现了 这个不错啊

 实在不想等了可以使用kill去杀sessiong_id