关于数据库死锁的问题

Question

我写的存储过程，主要语句有：
insert into T(code,amount)values('!@#$%',xx) ， 
update T set code=yy where code='!@#$%'，有老大说会发生死锁，给改成：
insert into T(amount)values(xx) ， 
set @currentid = @@identity， 
update T set code=yy where id = @currentid，气死，这两条语句有什么区别?到底怎么产生死锁，谁能给一个必然死锁的例子看看？.

Answer 1

--死鎖
/******************************************************************************************************************************************************
死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，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'
goif 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         CT2:
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 該事件發生了死鎖--阻塞   
/***********************************************************************************************************************   
阻塞：其中一个事务阻塞，其它事务等待对方释放它们的锁,同时会导致死锁问题。   
  
整理人：中国风(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     

Answer 2

--#1.死锁倒不敢说,但是如果code不是主键的话,你update时可能会有问题.
--#2.老大的方法也有一点问题,取@currentid时应该用SCOPE_IDENTITY()
insert into T(amount) values(xx)  
set @currentid = SCOPE_IDENTITY() 
update T set code=yy where id = @currentid

Answer 3

有人说：
select SCOPE_IDENTITY()
返回上面操作的数据表最后row的IDENTITY 列的值；返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值。一个作用域就是一个模块——存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。SELECT   @@IDENTITY返回上面操作最后一个数据表的最后row的IDENTITY 列的值；那我在存储过程中确实用SCOPE_IDENTITY()会好，谢谢Shawn。这样并发的时候就不会死锁或者更新错误么

Answer 4

你们写的两种效果都不一样。一个是按id更新 一个是安value更新。