阻塞分析
来源：互联网 发布：优质数据防泄密方案 编辑：程序博客网 时间：2024/05/17 20:24
--阻塞 /*********************************************************************************************************************** 阻塞：其中一个事务阻塞，其它事务等待对方释放它们的锁,同时会导致死锁问题。 整理人：中国风(Roy) 日期:2008.07.20 ************************************************************************************************************************/ --生成测试表Ta if not object_id('Ta') is null drop table Ta go create table Ta(ID int Primary key,Col1 int,Col2 nvarchar(10)) insert Ta select 1,101,'A' union all select 2,102,'B' union all select 3,103,'C' go 生成数据: /* 表Ta ID Col1 Col2 ----------- ----------- ---------- 1 101 A 2 102 B 3 103 C (3 行受影响) */ 将处理阻塞减到最少: 1、事务要尽量短 2、不要在事务中请求用户输入 3、在读数据考虑便用行版本管理 4、在事务中尽量访问最少量的数据 5、尽可能地使用低的事务隔离级别 go 阻塞1(事务): --测试单表 -----------------------------连接窗口1(update/insert/delete)---------------------- begin tran --update update ta set col2='BB' where ID=2 --或insert begin tran insert Ta values(4,104,'D') --或delete begin tran delete ta where ID=1 --rollback tran ------------------------------------------连接窗口2-------------------------------- begin tran select * from ta --rollback tran --------------分析----------------------- select request_session_id as spid, resource_type, db_name(resource_database_id) as dbName, resource_description, resource_associated_entity_id, request_mode as mode, request_status as Status from sys.dm_tran_locks /* spid resource_type dbName resource_description resource_associated_entity_id mode Status ----------- ------------- ------ -------------------- ----------------------------- ----- ------ 55 DATABASE Test 0 S GRANT NULL 54 DATABASE Test 0 S GRANT NULL 53 DATABASE Test 0 S GRANT NULL 55 PAGE Test 1:201 72057594040483840 IS GRANT 54 PAGE Test 1:201 72057594040483840 IX GRANT 55 OBJECT Test 1774629365 IS GRANT NULL 54 OBJECT Test 1774629365 IX GRANT NULL 54 KEY Test (020068e8b274) 72057594040483840 X GRANT --(spID:54请求了排它锁) 55 KEY Test (020068e8b274) 72057594040483840 S WAIT --(spID:55共享锁+等待状态) (9 行受影响) */ --查连接住信息(spid:54、55) select connect_time,last_read,last_write,most_recent_sql_handle from sys.dm_exec_connections where session_id in(54,55) --查看会话信息 select login_time,host_name,program_name,login_name,last_request_start_time,last_request_end_time from sys.dm_exec_sessions where session_id in(54,55) --查看阻塞正在执行的请求 select session_id,blocking_session_id,wait_type,wait_time,wait_resource from sys.dm_exec_requests where blocking_session_id>0--正在阻塞请求的会话的 ID。如果此列是 NULL，则不会阻塞请求 --查看正在执行的SQL语句 select a.session_id,sql.text,a.most_recent_sql_handle from sys.dm_exec_connections a cross apply sys.dm_exec_sql_text(a.most_recent_sql_handle) as SQL --也可用函数fn_get_sql通过most_recent_sql_handle得到执行语句 where a.Session_id in(54,55) /* session_id text ----------- ----------------------------------------------- 54 begin tran update ta set col2='BB' where ID=2 55 begin tran select * from ta */ 处理方法: --连接窗口2 begin tran select * from ta with (nolock)--用nolock:业务数据不断变化中,如销售查看当月时可用。 阻塞2(索引): -----------------------连接窗口1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --针对会话设置了 TRANSACTION ISOLATION LEVEL begin tran update ta set col2='BB' where COl1=102 --rollback tran ------------------------连接窗口2 insert into ta(ID,Col1,Col2) values(5,105,'E') 处理方法: create index IX_Ta_Col1 on Ta(Col1)--用COl1列上创索引,当更新时条件:COl1=102会用到索引IX_Ta_Col1上得到一个排它键的范围锁 阻塞3(会话设置): -------------------------------连接窗口1 begin tran --update update ta set col2='BB' where ID=2 select col2 from ta where ID=2 --rollback tran --------------------------------连接窗口2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED --设置会话已提交读:指定语句不能读取已由其他事务修改但尚未提交的数据 begin tran select * from ta 处理方法: --------------------------------连接窗口2(善用会话设置:业务数据不断变化中,如销售查看当月时可用) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --设置会话未提交读:指定语句可以读取已由其他事务修改但尚未提交的行 begin tran select * from ta
