阻塞分析
--阻塞
/***********************************************************************************************************************
阻塞：其中一個事務阻塞，其它事務等待對方釋放它們的鎖,同時會導致死鎖問題。 整理人：中國風(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
生成數(shù)據(jù):
/*
表Ta
ID Col1 Col2
----------- ----------- ----------
1 101 A
2 102 B
3 103 C (3 行受影響)
*/ 將處理阻塞減到最少:
1、事務要盡量短
2、不要在事務中請求用戶輸入
3、在讀數(shù)據(jù)考慮便用行版本管理
4、在事務中盡量訪問最少量的數(shù)據(jù)
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共享鎖+等待狀態(tài))
(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) --查看阻塞正在執(zhí)行的請求
select session_id,blocking_session_id,wait_type,wait_time,wait_resource
from sys.dm_exec_requests
where blocking_session_id>0--正在阻塞請求的會話的 ID。如果此列是 NULL，則不會阻塞請求 --查看正在執(zhí)行的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 --也可用函數(shù)fn_get_sql通過most_recent_sql_handle得到執(zhí)行語句
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:業(yè)務數(shù)據(jù)不斷變化中,如銷售查看當月時可用。 阻塞2(索引): -----------------------連接窗口1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --針對會話設(shè)置了 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列上創(chuàng)索引,當更新時條件:COl1=102會用到索引IX_Ta_Col1上得到一個排它鍵的范圍鎖 阻塞3(會話設(shè)置): -------------------------------連接窗口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 --設(shè)置會話已提交讀:指定語句不能讀取已由其他事務修改但尚未提交的數(shù)據(jù)
begin tran select * from ta 處理方法:
--------------------------------連接窗口2(善用會話設(shè)置:業(yè)務數(shù)據(jù)不斷變化中,如銷售查看當月時可用) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --設(shè)置會話未提交讀:指定語句可以讀取已由其他事務修改但尚未提交的行
begin tran select * from ta
轉(zhuǎn)載于:https://www.cnblogs.com/Roy_88/archive/2008/07/21/5463100.html
總結(jié)
- 上一篇： 感悟测试驱动开发
- 下一篇： 13 个应该记住的最不寻常的搜索引擎