数据库死锁的解决办法

近日在博客网站上，回复别人的数据库死锁避免问题，之前也曾经几次答复过同样的内容，觉得很有必要汇聚成一个博客文章，方便大家。

这里的办法，对所有的数据库都适用。

 

这个解决办法步骤如下：

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 和数据库死锁有点关系，将不胜感谢。