解决死锁
时间：2010-08-26 来源：snailshen
SQL code
--死鎖
/******************************************************************************************************************************************************
死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，SQL Server會通過回滾其中一個事務並返回一個錯誤來自已解決阻塞問題，讓其他事務完成它們的工作。 整理人：中国风(Roy) 日期:2008.07.20
******************************************************************************************************************************************************/ set nocount on ;
if object_id('T1') is not null
drop table T1
go
create table T1(ID int primary key,Col1 int,Col2 nvarchar(20))
insert T1 select 1,101,'A'
insert T1 select 2,102,'B'
insert T1 select 3,103,'C'
go if object_id('T2') is not null
drop table T2
go
create table T2(ID int primary key,Col1 int,Col2 nvarchar(20))
insert T2 select 1,201,'X'
insert T2 select 2,202,'Y'
insert T2 select 3,203,'Z'
go
生成表數據:
/*
T1:
ID Col1 Col2
----------- ----------- --------------------
1 101 A
2 101 B
3 101 C T2:
ID Col1 Col2
----------- ----------- --------------------
1 201 X
2 201 Y
3 201 Z
*/ 防止死鎖：
1、 最少化阻塞。阻塞越少，發生死鎖機會越少
2、 在事務中按順序訪問表（以上例子：死鎖2）
3、 在錯誤處理程式中檢查錯誤1205並在錯誤發生時重新提交事務
4、 在錯誤處理程式中加一個過程將錯誤的詳細寫入日誌
5、 索引的合理使用(以上例子：死鎖1、死鎖3)
當發生死鎖時，事務自動提交，可通過日誌來監視死鎖
死鎖1(索引):
--連接窗口1
--1步:
begin tran
update t1 set col2=col2+'A' where col1=101 --3步:
select * from t2 where col1=201
commit tran
--連接窗口2 --2步:
begin tran
update t2 set col2=col2+'B' where col1=203 --4步:
select * from t1 where col1=103
commit tran --連接窗口1:收到死鎖錯誤,連接窗口2得到結果: /*
訊息 1205，層級 13，狀態 51，行 3
交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/ --連接窗口2:得到結果 /*
----------- ----------- --------------------
3 103 C
*/ 處理方法:
--在t1、t2表的col1條件列建索引
create index IX_t1_col1 on t1(col1)
create index IX_t2_col1 on t2(col1)
go --連接窗口1
--1步:
begin tran
update t1 set col2=col2+'A' where col1=101 --3步:
select * from t2 with(index=IX_t2_col1)where col1=201 --因表數據少，只能指定索引提示才能確保SQL Server使用索引
commit tran --連接窗口2 --2步:
begin tran
update t2 set col2=col2+'B' where col1=203
--4步:
select * from t1 with(index=IX_t1_col1) where col1=103 --因表數據少，只能指定索引提示才能確保SQL Server使用索引
commit tran --連接窗口1:
/*
ID Col1 Col2
----------- ----------- --------------------
1 201 X (1 個資料列受到影響) */
--連接窗口2
/*
ID Col1 Col2
----------- ----------- --------------------
3 103 C (1 個資料列受到影響)
*/
死鎖2(訪問表順序): --連接窗口1:
--1步:
begin tran
update t1 set col1=col1+1 where ID=1 --3步:
select col1 from t2 where ID=1
commit tran --連接窗口2:
--2步:
begin tran
update t2 set col1=col1+1 where ID=1 --4步
select col1 from t1 where ID=1
commit tran
--連接窗口1: /*
col1
-----------
201 (1 個資料列受到影響)
*/ --連接窗口2: /*
col1
-----------
訊息 1205，層級 13，狀態 51，行 1
交易 (處理序識別碼 54) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/ 處理方法: --改變訪問表的順序 --連接窗口1:
--1步:
begin tran
update t1 set col1=col1+1 where ID=1 --3步:
select col1 from t2 where ID=1
commit tran --連接窗口2:
--2步:
begin tran
select col1 from t1 where ID=1--會等待連接窗口1提交
--4步
update t2 set col1=col1+1 where ID=1
commit tran 死鎖3(單表): --連接窗口1: while 1=1
update T1 set col1=203-col1 where ID=2 --連接窗口2:
declare @i nvarchar(20)
while 1=1
set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);--因表數據少，只能指定索引提示才能確保SQL Server使用索引 --連接窗口1
/*
訊息 1205，層級 13，狀態 51，行 4
交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/
處理方法:
1、刪除col1上的非聚集索引,這樣影響SELECT速度，不可取.
drop index IX_t1_col1 on t1
2、建一個覆蓋索引
A、drop index IX_t1_col1 on t1
B、create index IX_t1_col1_col2 on t1(col1,col2)
通過SQL Server Profiler查死鎖信息: 啟動SQL Server Profiler——連接實例——事件選取範圍——顯示所有事件
選擇項:
TSQL——SQL:StmtStarting
Locks——Deadlock graph(這是SQL2005新增事件，生成包含死鎖信息的xml值)
——Lock:DeadlockChain 死鎖鏈中的進程產生該事件，可標識死鎖進程的ID並跟蹤操作
——Lock:Deadlock 該事件發生了死鎖 阻塞分析：
http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx oracle锁表与解表
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null --kill session语句
alter system kill session'50,492';
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--1.查出锁定object的session的信息以及被锁定的object名
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
--2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
--比上面那段多出sql_text和action
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid; 这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
杀锁命令
alter system kill session 'sid,serial#'
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL 如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC ; --杀状态为killed的锁 ORA-00031: session marked for kill 处理Oracle中杀不掉的锁 一些ORACLE中的进程被杀掉后，状态被置为"killed"，但是锁定的资源很长时间不释放，有时实在没办法，只好重启数据库。现在提供一种方法解决这种问题，那就是在ORACLE中杀不掉的，在OS一级再杀。 1.下面的语句用来查询哪些对象被锁：
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid; 2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#) 【注】以上两步，可以通过Oracle的管理控制台来执行。 3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid） 4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345
ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done. Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner. kill -9 12345
--死鎖
/******************************************************************************************************************************************************
死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，SQL Server會通過回滾其中一個事務並返回一個錯誤來自已解決阻塞問題，讓其他事務完成它們的工作。 整理人：中国风(Roy) 日期:2008.07.20
******************************************************************************************************************************************************/ set nocount on ;
if object_id('T1') is not null
drop table T1
go
create table T1(ID int primary key,Col1 int,Col2 nvarchar(20))
insert T1 select 1,101,'A'
insert T1 select 2,102,'B'
insert T1 select 3,103,'C'
go if object_id('T2') is not null
drop table T2
go
create table T2(ID int primary key,Col1 int,Col2 nvarchar(20))
insert T2 select 1,201,'X'
insert T2 select 2,202,'Y'
insert T2 select 3,203,'Z'
go
生成表數據:
/*
T1:
ID Col1 Col2
----------- ----------- --------------------
1 101 A
2 101 B
3 101 C T2:
ID Col1 Col2
----------- ----------- --------------------
1 201 X
2 201 Y
3 201 Z
*/ 防止死鎖：
1、 最少化阻塞。阻塞越少，發生死鎖機會越少
2、 在事務中按順序訪問表（以上例子：死鎖2）
3、 在錯誤處理程式中檢查錯誤1205並在錯誤發生時重新提交事務
4、 在錯誤處理程式中加一個過程將錯誤的詳細寫入日誌
5、 索引的合理使用(以上例子：死鎖1、死鎖3)
當發生死鎖時，事務自動提交，可通過日誌來監視死鎖
死鎖1(索引):
--連接窗口1
--1步:
begin tran
update t1 set col2=col2+'A' where col1=101 --3步:
select * from t2 where col1=201
commit tran
--連接窗口2 --2步:
begin tran
update t2 set col2=col2+'B' where col1=203 --4步:
select * from t1 where col1=103
commit tran --連接窗口1:收到死鎖錯誤,連接窗口2得到結果: /*
訊息 1205，層級 13，狀態 51，行 3
交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/ --連接窗口2:得到結果 /*
----------- ----------- --------------------
3 103 C
*/ 處理方法:
--在t1、t2表的col1條件列建索引
create index IX_t1_col1 on t1(col1)
create index IX_t2_col1 on t2(col1)
go --連接窗口1
--1步:
begin tran
update t1 set col2=col2+'A' where col1=101 --3步:
select * from t2 with(index=IX_t2_col1)where col1=201 --因表數據少，只能指定索引提示才能確保SQL Server使用索引
commit tran --連接窗口2 --2步:
begin tran
update t2 set col2=col2+'B' where col1=203
--4步:
select * from t1 with(index=IX_t1_col1) where col1=103 --因表數據少，只能指定索引提示才能確保SQL Server使用索引
commit tran --連接窗口1:
/*
ID Col1 Col2
----------- ----------- --------------------
1 201 X (1 個資料列受到影響) */
--連接窗口2
/*
ID Col1 Col2
----------- ----------- --------------------
3 103 C (1 個資料列受到影響)
*/
死鎖2(訪問表順序): --連接窗口1:
--1步:
begin tran
update t1 set col1=col1+1 where ID=1 --3步:
select col1 from t2 where ID=1
commit tran --連接窗口2:
--2步:
begin tran
update t2 set col1=col1+1 where ID=1 --4步
select col1 from t1 where ID=1
commit tran
--連接窗口1: /*
col1
-----------
201 (1 個資料列受到影響)
*/ --連接窗口2: /*
col1
-----------
訊息 1205，層級 13，狀態 51，行 1
交易 (處理序識別碼 54) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/ 處理方法: --改變訪問表的順序 --連接窗口1:
--1步:
begin tran
update t1 set col1=col1+1 where ID=1 --3步:
select col1 from t2 where ID=1
commit tran --連接窗口2:
--2步:
begin tran
select col1 from t1 where ID=1--會等待連接窗口1提交
--4步
update t2 set col1=col1+1 where ID=1
commit tran 死鎖3(單表): --連接窗口1: while 1=1
update T1 set col1=203-col1 where ID=2 --連接窗口2:
declare @i nvarchar(20)
while 1=1
set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);--因表數據少，只能指定索引提示才能確保SQL Server使用索引 --連接窗口1
/*
訊息 1205，層級 13，狀態 51，行 4
交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/
處理方法:
1、刪除col1上的非聚集索引,這樣影響SELECT速度，不可取.
drop index IX_t1_col1 on t1
2、建一個覆蓋索引
A、drop index IX_t1_col1 on t1
B、create index IX_t1_col1_col2 on t1(col1,col2)
通過SQL Server Profiler查死鎖信息: 啟動SQL Server Profiler——連接實例——事件選取範圍——顯示所有事件
選擇項:
TSQL——SQL:StmtStarting
Locks——Deadlock graph(這是SQL2005新增事件，生成包含死鎖信息的xml值)
——Lock:DeadlockChain 死鎖鏈中的進程產生該事件，可標識死鎖進程的ID並跟蹤操作
——Lock:Deadlock 該事件發生了死鎖 阻塞分析：
http://blog.csdn.net/roy_88/archive/2008/07/21/2682044.aspx oracle锁表与解表
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null --kill session语句
alter system kill session'50,492';
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--1.查出锁定object的session的信息以及被锁定的object名
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
--2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
--比上面那段多出sql_text和action
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid; 这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
杀锁命令
alter system kill session 'sid,serial#'
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL 如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC ; --杀状态为killed的锁 ORA-00031: session marked for kill 处理Oracle中杀不掉的锁 一些ORACLE中的进程被杀掉后，状态被置为"killed"，但是锁定的资源很长时间不释放，有时实在没办法，只好重启数据库。现在提供一种方法解决这种问题，那就是在ORACLE中杀不掉的，在OS一级再杀。 1.下面的语句用来查询哪些对象被锁：
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid; 2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#) 【注】以上两步，可以通过Oracle的管理控制台来执行。 3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid） 4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345
ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done. Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner. kill -9 12345
相关阅读 更多 +
