SQL事务阻塞分析

2014年辛星mysql教程秋季版之夯实基础

• 类型：电子教程大小：8.5M语言：中文 评分：8.3
• 标签：

立即下载

--阻塞
/***********************************************************************************************************************
阻塞：其中一个事务阻塞，其它事务等待对方释放它们的锁,同时会导致死锁问题。

--生成测试表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