SQLServer事务、阻塞、死锁

阻塞和死锁是数据库应用的设计问题。从根本上来说，因为关系型数据库事务的原因，阻塞是必须的。

阻塞和死锁产生的三大因素：连接持有锁的时间过长、锁的粒度较大、数目过多。

锁产生的背景：事务。因为事务的ACID（原子性、一致性、隔离性、持久性）使得数据库在事务过程中，必须锁定要修改的资源。换句话说，阻塞是实现事务的隔离所带来的不可避免的代价。为了减少阻塞，可以从以下方面考虑：

1、申请锁的互斥度。

2、锁的范围和数目。

3、事务持有锁资源的时间长短。

一个矛盾：数据库在锁定一个较小的粒度上（行锁），可以提高并发度，但是数据库开销较大。锁定在较大的粒度上（表），开销较小，但是会降低并发度。

数据库引擎可以锁定的资源：RID（用于锁定堆（Heap）中的某一行）、KEY（用于锁定索引上的某一行，或者某个索引键）、PAGE（锁定一个数据页或者索引页）、EXTENT（一组连续的8页）、HoBT（表下的一个分区）、TABLE（表）、FILE（文件）、APPLICATION（应用程序专用的资源）、METADATA（元数据锁）、ALLOCATION_UNIT（分配单元）、DATABASE（整个数据库）。

锁模式：共享（S）、更新（U）、排他（X）、意向、架构、大容量更新、键范围。

共享锁：允许其他事务读取，不允许其他事务修改数据。

更新锁：更新锁可以看做是共享锁和排他锁的中间状态。当有两个事务同时要转换为排他锁，且这两个事务都相互等待对方释放共享锁时，此时会发生死锁。一次只有一个事务可以获得更新锁。事务真正修改的时候，更新锁会转换为排他锁。

排他锁：任何其他事务都不能读取和修改数据。

意向锁：告知其他事务，保护底层资源。

键范围锁：可序列化事务隔离级别时，可以防止幻读。

                                                                                                                                    锁模式兼容性

事务的隔离级别能影响锁得申请以及释放时间，语句执行计划能影响锁的粒度以及申请数量。

脏读：事务一修改数据A0为A1，事务二读取数据A1，事务一回滚。

不可重复读：事务一读取数据为A0，此时事务二修改数据为A1，事务一再次读取时数据变为A1。

幻读：事务一读取数据为A0，此时事务二增加数据A1，事务一再次读取时数据变为A0、A1。

隔离级别：

未提交读、

已提交读（可防止脏读）：读的时候加共享锁，读完就会释放共享锁。

可重复读（可防止脏读、不可重复读）：读的时候加共享锁，直到事务结束才释放，故其他事务不能修改读取的数据。

快照（可防止幻读）：读、写操作不会相互堵塞。

可序列化（最高级别）：加范围锁，锁定的数据既不能更改，而且满足锁定条件的数据也不能插入和删除。

阻塞如果发生在共享锁上，可以通过降低事务隔离级别得到缓解。如果发生在排他锁上面，则不能。

查看当前持有的锁：

select * from sys.dm_tran_locks;

sp_lock;

-- 查看锁在那些表和索引上面
SELECT A.request_session_id ,A.resource_type,A.resource_associated_entity_id,
A.request_status,A.request_mode,A.resource_description,P.object_id,
OBJECT_NAME(P.object_id) AS OBJECT_NAME,P.*
FROM SYS.dm_tran_locks A LEFT JOIN SYS.partitions P
ON A.resource_associated_entity_id=P.hobt_id
--WHERE A.resource_database_id=DB_ID('CNC_BI3')    --根据需要改为相应的数据库名称
ORDER BY A.request_session_id,A.resource_type,A.resource_associated_entity_id;

在查询语句执行的过程中，会给每一条读到的记录或者键值加共享锁。此时如果查询是全表扫描，而另外有一个事务在修改表中的数据，不管修改的数据是不是要查询的，都会阻塞查询。如果记录不返回，查询完后共享锁会被释放。如果记录返回，查询完后视隔离级别而定。

阻塞和死锁问题查找：

select * from master.sys.sysprocesses;    

--spid 小于50为系统会话，大于50为用户连接。
--查看blocked 大于 0的。为0表示不阻塞。-2 分布式事务；-3延迟的恢复事务；-4内部闩锁。如果blocked 为 52，说明当前语句被spid 为52 的语句阻塞。

阻塞问题查找步骤：

1、SELECT * FROM MASTER.SYS.sysprocesses    --查看是否阻塞
2、SELECT * FROM SYS.sysdatabases           -- 查询数据库名称
   或者SELECT DB_NAME(7)
3、SP_LOCK                                  -- 查看阻塞
   或者
   SELECT CONVERT(smallint,a.req_spid) as spid,
a.rsc_dbid as dbid,
rsc_objid as ObjId,
rsc_indid as IndId,
SUBSTRING(v.name,1,4) as Type,
SUBSTRING(a.rsc_text,1,32) as Resource,
SUBSTRING(u.name,1,8) as Mode,
SUBSTRING(x.name,1,5) as Status
FROM master.dbo.syslockinfo a,
    master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
WHERE a.rsc_type=v.number
and v.type='LR'
and a.req_status=x.number
and x.type='LS'
AND a.req_mode+1=u.number
and u.type='L'
and SUBSTRING(x.name,1,5)='WAIT'
order by spid
4、select OBJECT_NAME(245575913)   -- 查询表名称
   select * from sys.indexes a where a.object_id=245575913    -- 查询索引名称


5、select p.session_id,p.request_id,p.start_time,                  --查找阻塞的语句（正在运行）
p.status,p.command,p.blocking_session_id,p.wait_type,p.wait_time,
p.wait_resource,p.total_elapsed_time,p.open_transaction_count,
p.transaction_isolation_level,
SUBSTRING(qt.text,p.statement_start_offset/2,
       (case when p.statement_end_offset=-1
  then len(convert(nvarchar(max),qt.text))*2
  else p.statement_end_offset
  end -statement_start_offset)/2) as "SQL statement",
p.statement_start_offset,p.statement_end_offset,batch=qt.text
   from master.sys.dm_exec_requests p
   cross apply sys.dm_exec_sql_text(p.sql_handle) as qt
   where p.session_id>50


DBCC INPUTBUFFER(SPID)             --获得最后一个批处理语句

死锁问题查找：系统自动检测死锁，时间间隔为5S，检测出死锁后，会选择一个回滚事务比较小的事务作为死锁的牺牲品。

DBCC TRACEON(1222,-1)   -- 打开死锁跟踪标识，发生死锁时，会将死锁信息写入ERRORLOG 日志。
DBCC TRACEOFF(1222,-1)  -- 关闭死锁跟踪标识，发生死锁时，不会将死锁信息写入ERRORLOG 日志。

死锁解决方法：

1、按同一顺序访问对象。
2、避免事务中的用户交互。
3、保持事务简短并处于一个批处理中。
4、使用较低的隔离级别。
5、调整语句的执行计划，减少锁的申请数目。