近日在博客网站上，回复别人的数据库死锁避免问题，之前也曾经几次答复过同样的内容，觉得很有必要汇聚成一个博客文章，方便大家。
这里的办法，对所有的数据库都适用。
这个解决办法步骤如下：
1. 每个表中加 updated_count (integer) 字段
2. 新增一行数据,updated_count =0 :
insert into table_x (f1,f2,...,update_count) values(...,0);
3. 根据主键获取一行数据 SQL，封装成一个 DAO 函数(我的习惯是每个表一个 uuid 字段做主键。从不用组合主键，组合主键在多表 join 时 SQL 写起来很麻烦；也不用用户录入的业务数据做主键，因为凡是用户录入的数据都可能错误，然后要更改，不适合做主键)。
select * from table_x where pk = ?
4. 删除一行数据
4.1 先通过主键获取此行数据, 见 3.
4.2 delete from table_x where pk = ? and update_count=? , 这里 where 中的 update_count 通过 4.1 中获取
4.3 检查 4.2 执行影响数据行数，如果删除失败，则是别人已经删除或者更新过同一行数据，抛异常，在最外面 rollback，并通过合适的词语提醒用户有并发操作，请稍候再试。
int count = cmd.ExecuteNonQuery();
if(udpatedCount < 1){
throw new Exception(“检测到并发操作，为防止死锁，已放弃当前操作，请稍候再试，表 xxx, 数据 key ….”);
}
5. 更新一行数据
5.1 先通过主键获取此行数据, 见 3.
5.2 update table_x set f1=?,f2=?, ...,update_count=update_count+1 where pk = ? and update_count=? , 这里where 中的 update_count 通过 5.1 中获取
5.3 检查 5.2 执行影响数据行数，如果更新失败，则是别人已经删除或者更新过同一行数据，抛异常，在最外面 rollback，并通过合适的词语提醒用户有并发操作，请稍候再试。
int count = cmd.ExecuteNonQuery();
if(udpatedCount < 1){
throw new Exception(“检测到并发操作，为防止死锁，已放弃当前操作，请稍候再试，表 xxx, 数据 key ….”);
}
6. 数据库访问层 DAO 中，绝对不要写 try catch，也不要写 commit/rollback. 因为当我写了一个 dao1.insert(xxx) ，另一个人写了 dao2.insert(xxx), 两周后有可能会有人把这两个函数组合在一起放在一个事务中。如果dao1.insert(xxx)已经 commit ，那么dao2.insert(xxx) 中rollback 会达不到期望效果。很多电脑书中示例代码，都有这个错误。
数据库事务应该是这样界定起始范围:
6.1 单机版程序，每个按钮操作，对应一个事务。可以在把 connection/transaction 传递到 dao 中。在按钮响应的代码处，处理事务。catch 到任何 Exception 都要 rollback.
6.2 网页版程序，每个按钮操作，对应一个事务。可以在把 connection/transaction 传递到 dao 中。在按钮响应的代码处，处理事务。我强烈建议对于 Web应用，数据库连接的打开/关闭、数据库事务的开始和 commit/rollback 全在 filter 中处理(Java EE 和 ASP.NET MVC 都有 filter, 其它的不知道)，事务、数据库连接通过 threadlocal 传入到 DAO 中。filter 中 catch 到任何 Exception 都要 rollback.
见过很多用 Spring 的人，代码中启动了几个数据库事务自己都不知道，符不符合自己的需要，也不知道。我的建议是，禁止使用 Spring 管理数据库事务。
7. 单表的增、删、改、通过主键查，应该用工具自动生成。自动生成代码，应该放在单独一个目录，以便后面有数据库表改动，可以重新生成代码并覆盖。自动生成的文件，在第一行就写上注释，表示这是一个自动生成的文件，以后会被自动覆盖，所以不要改这个文件。
举例来说，对于 tm_system_user 表，可以自动生成 TmSystemUserDAO, 包含函数: insert(TmSystemUser), update(TmSystemUser), delete(TmSystemUser), getByKey(key), batchInsert(TmSystemUser[])。
8. 总是使用事务，并用 ReadCommited 级别，即使是纯查询 SQL，也这么写。这可以简化设计与写代码，没有发现明显多余的性能消耗。
9. 数据设计时，尽量避免 update/delete. 举例来说，如果是一个请假条的审批流程，把请假条申请设计成一个表，领导批复设计成另一个表。尽量避免设计时合并成一个表，把批准状态(同意/否决)、批准时间当成“请假条申请”的属性。
说极端一点，最好从数据库设计上，避免后续编程有 update/delete， 只有 insert。 好像现在流行的 NoSQL 也是这么个思路。
10. 补充，如果在后台检查页面录入数据，报错处理，有以下两种方法：
10.1 只要有一个错误，就 throw exception.
10.2 把所有的错误都检测出来，比如，用户名未录入，电子邮件未录入，放在一个 List中，然后 throw exception.
2012-3-30, 由于很多网友对数据库死锁了解不深，甚至有部分网友，不知道数据库会死锁僵住，特补充一些资料。以下内容，节选自《LINQ实战》：
8.1.1 悲观式并发
在.NET出现之前，很多应用程序都需要自行管理与数据库之间的连接。在这些系统中，开发人员经常在获取某条记录之后为其加锁，用来阻止其他用户可能在同时作出的修改。此类加锁的策略就叫做悲观式并发。悲观式并发对于某些小型的Windows桌面程序来讲可能没有什么问题，不过若是在用户很多的大型系统中使用同样的策略，那么系统的整体性能很快就会被拖累下来。
随着系统规模的扩大，可伸缩性问题开始浮出水面。因此，很多系统从客户端-服务器架构迁移到了更少状态信息的、基于Web的应用程序，这也同时降低了部署的成本。无状态的Web应用程序也让过于保守的悲观式并发策略再无用武之地。
为了让开发者避免陷入到悲观式并发所带来的可伸缩性以及加锁的泥沼中，.NET Framework在设计之初就考虑到了Web应用程序的离线特性。.NET以及ADO.NET所提供的API均无法锁住某张数据表，这样自然就终结了悲观式并发的可能。不过如果需要的话，应用程序同样能在第一次获取某条记录的同时为其添加一个"签出"标签，这样在第二次尝试访问时，即可获得该"签出"情况，并根据需要进行相应的处理。不过很多情况下，由于很难确定用户是否不再使用这个标签，因此"签出"标签会经常处于未重新设置状态。正因为这样，悲观式并发在离线程序中的使用频率也越来越低。
8.1.2 乐观式并发
由于离线环境下的程序常常不适合使用悲观式并发，因此另一种处理的策略，即乐观式并发逐渐出现在人们的视线中。乐观式并发允许任意多的用户随时修改他们自己的一份数据的拷贝。在提交修改时，程序将检查以前的数据是否有所改变。若没有变化，则程序只需保存修改即可。若发生了变化并存在冲突，那么程序将根据实际情况决定是将前一修改覆盖掉，还是把这一次新的修改丢弃，或是尝试合并两次修改。
乐观式并发的前一半操作相对来说比较简单。在不需要并发检查的情况下，数据库中使用的SQL语句将类似于如下语法：UPDATE TABLE SET [field = value] WHERE [Id = value]。不过在乐观式并发中，WHERE子句将不只包含ID列，同时还要比较表中其他各列是否与原有值相同。
在代码清单8-1中，我们在最后通过检查RowCount来查看这次更新是否成功。若RowCount为1，则表明原有记录在该用户修改的期间并没有被别人更新，即更新成功。若RowCount为0，则意味着有人在期间修改了该记录。此时该记录将不会被更新，程序也能够告知用户有关该冲突的信息，并根据需要执行合适的操作...
2012-3-31 补充：
Oracle中的TimeStamp(时间戳)与SqlServer中的差别很大。SqlServer中的TimeStamp是二进制格式存储在数据库中，可以将DataSet中的这个字段类型设定为base64Binary类型。Oracle中的TimeStamp是时间格式存储的。
SQL Server 有个函数名叫 CURRENT_TIMESTAMP，与 SqlServer中的TimeStamp 数据列类型，没有一毛钱的关系。
个人认为 SqlServer中的TimeStamp 数据列类型，属于“名词乱用”，与一般人理解中的 timestamp 不是一个意思。继续从互联网上查找，果然有发现：
Transact-SQL timestamp 数据类型与在 SQL-92 标准中定义的 timestamp 数据类型不同。SQL-92 timestamp 数据类型等价于 Transact-SQL datetime 数据类型。
Microsoft SQL Server 将来的版本可能会修改 Transact-SQL timestamp 数据类型的行为，使它与在标准中定义的行为一致。到那时，当前的 timestamp 数据类型将用 rowversion 数据类型替换。
Microsoft SQL Server 2000 引入了 timestamp 数据类型的 rowversion 同义词。在 DDL 语句中尽可能使用 rowversion 而不使用 timestamp。rowversion 受数据类型同义词行为的制约。
没有看出 SQL Server timestamp 和数据库死锁有何关系！！！即使是微软 LINQ for SQL 自动生成的代码，也是没有用到 timestamp ，而是用了本博客文章中的技术。 如有哪位网友提供资料，说明 SQL Server timestamp 和数据库死锁有点关系，将不胜感谢。
举几个例子
1 没看出多加一个字段的用途在哪里
2 步骤4.1和4.2是不是同一个事务，如果不是同一个事务那么在之间会受其他进程影响，如果在同一个事务中，这个事务的隔离基本至少要达到repeatable read才可以不受其他进程影响，而不是你说的read committed。而这个方法本身就毫无意义，例如在SQL Server中可以用select with updlock，oracle中可以用select for update达到更好的效果。
3 你这些方法只是针对单条操作有效，实如果所有进程都只操作一条数据，而且还是根据主键来操作，死锁本来就完全可控了。实际操作中死锁多是由于escalate上来的page锁range锁导致。
4 楼主对索引的想法过于美好。使用UUID做主键没什么问题，但是主键过长导致性能损失很厉害，更别提空间浪费。淘宝网用了几个月时间才把所有的UUID主键都改为自增长整数。
5 复合主键或者复合索引的应用非常广泛，对性能有必要的提升，而且复合主键和多表join有什么关系？该怎么join就怎么join
6 避免重复commit或者rollback事务是SQL基本常识，在commit之前先判断事务状态，例如xact_state
7 现在主流的数据库系统都已经支持在SQL级别做异常处理了，楼主每次都判断返回行数的方法写到SP里效率更高
1. 多加一个字段, 用来检测并发修改可能导致的死锁，用在 update/delete 的 SQL 中，怎么会没有用呢？
2. 4.1和4.2 是一个事务，read committed 是希望读到其它进程事务的影响，不然怎么进行并发写的检测？猫井三宫兄没有看明白。我就是希望程序员在不用 select with updlock/select for update 的情况下就系统就能自动检测死锁，这样不是更省事？怎么叫“毫无意义”？你的做法，要求所有的程序员都在每个地方正确写 select for update，要求太高了吧？漏掉几个，不就是死锁出来了？
我的做法，程序员根本就不需要知道 select for update，根本不会有出错的可能。
3. 对 update/delete ，本来绝大多数系统，都可以一行行处理，按我的做法，select/update/delete 是不会死锁的。
4. uuid 影响性能，是想当然吧？从未见过有人认真测试后得出这个结论，也从未见过哪本权威一点的书提到。
5. 复合主键提高性能？想想都不可能。我曾经见过一个基础数据表用 5 个字段做复合主键，然后这个表每次与其它表 join 的时候，都要 where 中写 5 个字段，导致 SQL 又臭又长，改成 uuid 主键之后，SQL 清晰多了。
6. 我不觉得避免 commit 是基本常识。在commit之前先判断事务状态，有什么好处？如果没有好处，判断做什么？没事瞎逛逛？
7. 我的设计是尽量避免用 SP，这样可以让代码跨数据库。至于写到SP里效率更高，考虑到网络传输 SQL、数据库运行 SQL的时间，用不用 SP 并没有特别的效率变化。
至于空间浪费，我相信对绝大多数系统来说，磁盘空间不够有几个会是因为 uuid 太长导致？
我刚毕业的时候，给别人做系统，用身份证做主键，后来身份证号码升级了，加位数了，折腾了好久才搞定。如果用 uuid 做主键，那么这么麻烦？
至于自增长主键，不好意思，我曾经见过增长到很大后变成 0 从头增长的系统，导致数据主键重复出错，还是免了吧。
我觉得即使是 update/delete 的更新数据操作，用不用事务，也不会太影响速度:
a. 数据库事务写数据的大概逻辑可能是,写数据并注明这是草稿状态，数据写完后，commit 的时候，把草稿状态改成正式状态。
b. 数据库事务写数据的大概逻辑可能是,写数据并注明这是草稿状态，数据写完后，不需要等 commit，把草稿状态改成正式状态。
这如同我们在 windows /linux 中移动文件，并不一定是“复制成目标文件、删除原文件”，有可能只是在文件系统的目录登记中，更改一下文件所在目录结构，文件内容本身在磁盘上根本没有改动。
难道有人认为，数据库事务，是先写数据并注明这是草稿状态，commit 的时候，把草稿状态数据复制成正式状态数据，然后删除草稿状态数据？这也太笨拙了吧？
具体测试代码如下：
创建测试表和测试数据
CREATE TABLE TABLEA
(
OID INT NOT NULL,
F1 INT NULL,
UPDATE_COUNT INT NULL,
CONSTRAINT [PK_TABLEA] PRIMARY KEY CLUSTERED (OID ASC)
)
go
CREATE TABLE TABLEB
(
OID INT NOT NULL,
F1 INT NULL,
UPDATE_COUNT INT NULL,
CONSTRAINT [PK_TABLEB] PRIMARY KEY CLUSTERED (OID ASC)
)
go
INSERT INTO TABLEA VALUES (1, 1, 0)
INSERT INTO TABLEB VALUES (2, 2, 0)
go
打开两个查询窗口：
1.在A窗口执行：
BEGIN TRAN
UPDATE TABLEA SET F1 = 3, UPDATE_COUNT = UPDATE_COUNT + 1 WHERE OID = 1
2.在B窗口执行：
BEGIN TRAN
UPDATE TABLEB SET F1 = 4, UPDATE_COUNT = UPDATE_COUNT + 1 WHERE OID = 2
3.在A窗口执行：
UPDATE TABLEB SET F1 = 3, UPDATE_COUNT = UPDATE_COUNT + 1 WHERE OID = 2
4.在B窗口执行：
UPDATE TABLEA SET F1 = 4, UPDATE_COUNT = UPDATE_COUNT + 1 WHERE OID = 1
我的测试结果是：一个正常运行结束，一个由SQL server 检测到死锁抛异常，详细步骤如下：
1. 从菜单中启动 SQL Server Management Studio Express, 打开数据库，新建查询窗口，写上注释 --sql_win1， 这个注释在后面的步骤中，不要删除，一直保留
2. 从菜单中启动 SQL Server Management Studio Express, 打开数据库，新建查询窗口，写上注释 --sql_win2， 这个注释在后面的步骤中，不要删除，一直保留
3. sql_win1 执行 set transaction isolation level read committed;
4. sql_win1 执行 Begin Tran;
5. sql_win1 执行 SELECT * FROM TABLEA; 看到数据 (1,1,0) ----(我们的规则是 update 之前必须 select 出 UPDATE_COUNT)
6. sql_win1 执行 UPDATE TABLEA SET F1 = 3, UPDATE_COUNT = UPDATE_COUNT + 1 WHERE OID = 1 and UPDATE_COUNT =0;
7. sql_win1 执行 SELECT * FROM TABLEA; 看到数据 (1,3,1)
8. sql_win2 执行 set transaction isolation level read committed;
9. sql_win2执行 Begin Tran;
10. sql_win2 执行 SELECT * FROM TABLEB; 看到数据 (2,2,0)
11. sql_win2 执行 UPDATE TABLEB SET F1 = 4, UPDATE_COUNT = UPDATE_COUNT + 1 WHERE OID = 2 and UPDATE_COUNT =0;
12.sql_win1 执行：SELECT * FROM TABLEB;查询窗口被锁住，----(我们的规则是 update 之前必须 select 出 UPDATE_COUNT)
13.sql_win2 执行 SELECT * FROM TABLEA; 查询窗口被锁住，----(我们的规则是 update 之前必须 select 出 UPDATE_COUNT)
14. sql_win2 出错停止:"消息 1205，级别 13，状态 51，第 1 行,事务(进程 ID 67)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。"
15. 在 sql_win2 中 rollback.
16. sql_win1 中查询结果出来了，数据还是 (2,2,0)，也就是说，由于 sql_win2 中没有 commit 退出，不影响 sql_win1。这里的数据行版本号还是 0。
17. sql_win1 中继续执行 UPDATE TABLEB SET F1 = 3, UPDATE_COUNT = UPDATE_COUNT + 1 WHERE OID = 2 and UPDATE_COUNT =0;
18. sql_win1 中执行 commit;
19. 结论，此种情况下，sql_win1 成功执行，sql_win2 出错 rollback. 无死锁。
从《LINQ实战》中找了一些资料，加在本博客后，也是与我同样的办法。
"SQLserver 时间戳"有两个含义：
a) 是一种数据类型，微软特地关照，不要用这个字段做数据库主键。
b) 可以用来检测可能造成死锁的并发修改，比如 update... set t = .. where key = .. and t= ...。效果同我这里的版本号相同。
难道大虾对"SQLserver 时间戳"一词，另有发现？
据 Hibernate 文档中讲，用时间戳不如用版本号(Timestamps are a less reliable way of optimistic locking than version numbers)，大侠是不是有不同的看法？
http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch05.html
据 Hibernate 文档中讲，用时间戳不如用版本号(Timestamps are a less reliable way of optimistic locking than version numbers)，大侠是不是有不同的看法？
http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch05.html
1. 代码不按照你的1 2 3 4 5这种方式写，在面临死锁问题时结果是一样的，这个代码根本没有解决死锁问题，甚至对预防死锁的发生没有任何帮助。
2. 你这个是乐观锁，解决了部分并发更新检测问题，“并发更新”跟“死锁”是两个方面的问题，两码事。
3. 你说的线程长时间等待，更多的是阻塞（blocking）问题，阻塞跟死锁产生的根本原因都是并发，表现出来的症状，有时候是频繁死锁报错，有时候是大面积的阻塞，系统整体性能急剧衰减。深入的去了解一下数据库理论，尤其是事物、并发理论这块，才是彻底解决阻塞跟死锁的根本。
4. 友情提示一下，尽量避免阻塞跟死锁的一个基本做法是：
对不同的表，都按照相同的顺序进行更新各个表；对同一个表的数据，也按照相同的顺序更新各条记录。
基本上主流数据库都有死锁检测机制，你不需要做任何处理，最终数据库都会报给你一个检“测到死锁，某进程被作为牺牲品。。。”====不是这样的。数据库确有死锁检测机制，但数据库死锁、无人工干预一直死锁，这个在 Oracle 、SQL Server 上都有。
我这边做的是“应用级别的死锁检测”，经过实战考验，确实有效。
特别强调一点，“都按照相同的顺序进行更新各个表”很难实现，特别是较大系统，不值得推荐。而按本博文的方法，不需要按照相同的顺序进行更新各个表，就可以做到“死锁自动检测”。也就是说，本文所说，是更容易实现的有效办法。
扫一扫
1480
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
