在线情况
楼主
  • 头像
  • 级别
  • 徽章
  • 职务总版主
  • 声望+14
  • 积分1530
  • 经验278129
  • 文章2378
  • 注册2003-06-14
sql server阻塞分析
--阻塞 ?
/*********************************************************************************************************************** ?
阻塞:其中一个事务阻塞,其它事务等待对方释放它们的锁,同时会导致死锁问题。 ?
?
整理人:中国风(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 ?
LeadBBS 官方论坛(asp论坛)感谢您的参与
Page created in 0.0000 seconds with 4 queries.