SQL SERVER 阻塞与死锁PPT课件

1、.,1,SQL Server 2008新特征,阻塞与死锁,DBA,.,2,内容,DBA,.,3,事务,事务：单个工作单元执行的一些列操作。 事务的属性： A：原子性(Atomicity) 全做或全不做 C：一致性(Consistency) 多个事务对data访问间不冲突 I：隔离性(Isolation) 用锁进行隔离 D：持久性(Durability) 运行模式： 自动提交事务：每个语句一个事务 显示事务 BEGIN TRAN COMMIT TRAN ROLLBACK TRAN 隐示事务,DBA,.,4,并发,并发：多用户同时对一种资源进行访问 并发影响： 并发控制：A的修改不会对B做的修改产

2、生负面影响 悲观并发控制：加锁成本 rollback 隔离级别,DBA,.,5,隔离级别,定义一个事务必须与其他事务所进行的资源或数据更改相隔离的程度 隔离级别,DBA,.,6,锁定(locking),概念：(LOCK)事务获取的一种控制资源，保护数据，防止其他事务对数据进行冲突的或不兼容的访问。 可锁定资源 锁模式 锁兼容性 自定义锁定 锁升级：细粒度的锁升级为粗粒度的锁，SQL Server2008 可以用LOCK_ESCALATION显示禁止锁升级。,DBA,.,7,可锁定资源,DBA,.,8,锁模式(lock mode),DBA,.,9,锁兼容性,控制多个事务能否同时获取一个资源上的锁

3、。兼容时才能获得。,DBA,.,10,阻塞(blocking),概念 前提：一事务持有数据资源上的锁 动作：另一事务请求相同资源上的不兼容锁 后果：新的锁请求被阻塞，请求等待至锁被释放 实例： Connection1：获得某行的排他锁 Connection2：申请同一行的共享锁,DBA,.,11,检测阻塞,1. SP_WHO/SP_LOCK,DBA,.,12,检测阻塞,2. SYS.DM_TRAN_LOCKS SELECT request_session_id as 服务器进程标识符, resource_type as 被锁定资源类型, request_mode as 锁模式, request

4、_status as 请求状态 FROM sys.dm_tran_locks,DBA,.,13,检测阻塞,3. Other sys.dm_exec_connections(阻塞链中进程相关联接信息) -可了解联接中最后读写的时间及SQL语句 select * from sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)as ST where session_id IN(55,56); sys.dm_exec_sessions (查询阻塞中涉及的会话) sys.dm_exec_reque

5、sts (活动请求信息),DBA,.,14,终止和避免阻塞,Kill SET LOCK_TIMEOUT timeout_period,DBA,.,15,死锁,DBA,概念：多任务中，每个任务锁定了其他任务试图锁定的资源，导致的任务永久阻塞。 例子： CREATE TABLE Table1(Col1 int default(0); CREATE TABLE Table2(Col1 int default(0); INSERT INTO Table1 VALUES(1); INSERT INTO Table2 VALUES(1); Begin Tran Update Table1 Set Col1

6、=Col1+1; WaitFor Delay 00:01:00; SELECT * FROM Table2 Rollback Tran; Begin Tran Update Table2 Set Col1=Col1+1; WaitFor Delay 00:01:00; SELECT * FROM Table1 Rollback Tran;,.,16,死锁检测与查看,DBA,死锁检测：锁监视线程。定期搜索数据库引擎实例的所有任务。检测到死锁后线程选择一个死锁牺牲品来结束死锁。死锁牺牲品的事务以1025错误返回到应有程序并释放事务持有的锁。默认选择回滚开销最小的会话作为牺牲品。DEADLOCK_P

7、RIOPRTY低的死锁被牺牲。 死锁查看工具： 1024：报告死锁涉及的每个节点设置格式信息。 1222：设置死锁信息的格式，先进程后资源。 事件探查器死锁图形事件。,.,17,降低死锁的方法,DBA,按同一顺序访问对象。(注：避免出现循环) 避免事务中的用户交互。(注：减少持有资源的时间，较少锁竞争) 保持事务简短并处于一个批处理中。(注：同(2)，减少持有资源的时间) 使用较低的隔离级别。使用较低隔离级别比使用高隔离级别持有共享锁的时间更短。 使用基于行版本控制的隔离级别 READ_COMMITTED_SNAPSHOT设置为ON，读操作为行版本控制而不是共享锁 ALLOW_SNAPSHOT_ISOLATION设置为ON，快照隔离也使用行版本控制 使用绑定连接,.,18,死锁处理,DBA,方法一：EXEC sp_who active看哪个引起的阻塞，blk = 53；或者exec sp_lock 看哪个spid 处于wait状态，通过kill spid干掉相关进程。 方法二：设定锁超时： SELECT LOCK_TIMEOUT SET LOCK_TIMEOUT 5000 SQL Server内部有一个锁监视器线程执行死锁检查自动处理。 在select语句上加表级锁WITH(no lock)，此方法有可能导致脏读，因