数据库死锁的解决办法

Posts - 31 Articles - 0 Comments - 817 数据库死锁的解决办法

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

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

这个解决办法步骤如下：

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 updat e_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 和https://www.360docs.net/doc/199905448.html, MVC 都有filter, 其它的不知道)，事务、数据库连接通过threadlocal 传入到DAO 中。filter 中catch 到任何Exception 都要rollback.

见过很多用Spring 的人，代码中启动了几个数据库事务自己都不知道，符不符合自己的需要，也不知道。我的建议是，禁止使用Spring 管理数据库事务。

7. 单表的增、删、改、通过主键查，应该用工具自动生成。自动生成代码，应该放在单独一个目录，以便后面有数据库表改动，可以重新生成代码并覆盖。自动生成的文件，在第一行就写上注释，表示这是一个自动生成的文件，以后会被自动覆盖，所以不要改这个文件。

举例来说，对于tm_system_user 表，可以自动生成TmSystemUserDAO, 包含函数: insert(TmSys temUser), update(TmSystemUser), delete(TmSystemUser), getByKey(key), batchInsert(TmSyste mUser[])。

8. 总是使用事务，并用ReadCommited 级别，即使是纯查询SQL，也这么写。这可以简化设计与写代码，没有发现明显多余的性能消耗。

9. 数据设计时，尽量避免update/delete. 举例来说，如果是一个请假条的审批流程，把请假条申请设计成一个表，领导批复设计成另一个表。尽量避免设计时合并成一个表，把批准状态(同意/否决)、批准时间当成“请假条申请”的属性。

说极端一点，最好从数据库设计上，避免后续编程有update/delete，只有insert。好像现在流行的NoSQL 也是这么个思路。

10. 补充，如果在后台检查页面录入数据，报错处理，有以下两种方法：

10.1 只要有一个错误，就throw exception.

10.2 把所有的错误都检测出来，比如，用户名未录入，电子邮件未录入，放在一个List中，然后t hrow exception.

2012-3-30, 由于很多网友对数据库死锁了解不深，甚至有部分网友，不知道数据库会死锁僵住，特补充一些资料。以下内容，节选自《LINQ实战》：

8.1.1悲观式并发

在.NET出现之前，很多应用程序都需要自行管理与数据库之间的连接。在这些系统中，开发人员经常在获取某条记录之后为其加锁，用来阻止其他用户可能在同时作出的修改。此类加锁的策略就叫做悲观式并发。悲观式并发对于某些小型的Windows桌面程序来讲可能没有什么问题，不过若是在用户很多的大型系统中使用同样的策略，那么系统的整体性能很快就会被拖累下来。

随着系统规模的扩大，可伸缩性问题开始浮出水面。因此，很多系统从客户端-服务器架构迁移到了更少状态信息的、基于Web的应用程序，这也同时降低了部署的成本。无状态的Web应用程序也让过于保守的悲观式并发策略再无用武之地。

为了让开发者避免陷入到悲观式并发所带来的可伸缩性以及加锁的泥沼中，.NET Framework在设计之初就考虑到了Web应用程序的离线特性。.NET以及https://www.360docs.net/doc/199905448.html,所提供的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中的Time Stamp是时间格式存储的。

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