sql server阻塞分析

楼主
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  
<pre></pre>

电脑版 Page created in 0.0703 seconds with 4 queries.