数据库行锁争用问题分析

lsekfe

某系统的批量程序和联机程序同时发起交易、操作数据库同一张表时，出现了交易间相互影响导致交易失败。
　　分析原因为事务A对数据库表操作全表删除时，数据库机制会自动触发产生排他行锁，当行锁数达到“升级到表锁前允许的最大行锁数”时，会自动升级为表锁。但因事务B已对数据库表产生了排他行锁，则事务A自动升级表锁失败。直至事务A消耗掉了数据库“最大锁资源数”，系统会报错“锁资源耗尽”、交易失败。
　　因此，设计程序时，如涉及多事务操作数据库同一张表，需考虑事务间的关联影响，是否会引发事务冲突。
　　下文将详细描述和分析该问题。
　　1 数据库锁
　　首先介绍一下数据库锁的相关概念。
　　1.1 行锁、页锁和表锁
　　行锁是访问数据库的时候，锁定一行或者多行记录。页锁是访问数据库的时候，锁定查询结果所在页。表锁是访问数据库的时候，锁定整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。行锁的优点是锁定粒度小，发生锁冲突的概率低，并发度高；缺点是开销大，加锁慢，会出现死锁。表锁的优点是开销小，加锁快，不会出现死锁；缺点是锁定力度大，发生锁冲突概率高，并发度最低。页锁的开销和加锁速度介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。
　　共享锁 (S锁) 也称为读锁，用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。其封锁粒度包括行级、页级和表级。
　　排他锁（X锁）也称为独占锁、写锁，用于数据修改操作，例如INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。我们在操作数据库的时候，可能会由于并发问题而引起的数据的不一致性（数据冲突）。其封锁粒度也包括行级、页级和表级。
　　1.2 意向锁
　　意向锁的产生原因：解决表锁与之前可能存在的行锁冲突，避免为了判断表是否存在行锁而去扫描全表的系统消耗。
　　作用：一种快速判断表锁与之前可能存在的行锁冲突的机制。（数据库会帮我们自动加）
　　行锁在加锁前要先加意向锁。意向锁是一种表锁。
　　事例分析：事务 A 锁住了表中的一行，让这一行只能读，不能写。之后，事务 B 申请整个表的写锁。如果事务 B 申请成功，那么理论上它就能修改表中的任意一行，这与 A 持有的行锁是冲突的。
　　数据库需要避免这种冲突，就是说要让 B 的申请被阻塞，直到 A 释放了行锁。数据库要怎么判断这个冲突呢？
　　step1：判断表是否已被其他事务用表锁锁表。
　　step2：判断表中的每一行是否已被行锁锁住。
　　注意step2，因为需要遍历整个表，从而导致判断方法的效率不高。为了更有针对性的进行判断，便产生了意向锁的概念。
　　在意向锁存在的情况下，事务 A 必须先申请表的意向共享锁，成功后再申请一行的行锁。
　　在意向锁存在的情况下，上面的判断可以改成：
　　step1：不变。
　　step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务 B 申请表的写锁会被阻塞。
　　意向锁分为意向共享锁（共享锁定）和意向排他锁（排他锁定），且意向锁是表级别锁。
　　例子-执行update：
　　1> update PD set workdate = '20200603' where PDid ='23'
　　2> go
　　(1 row affected)
　　1> sp_lock3x
　　2> go
　　返回结果：

　　解释：
　　Sh--共享锁（S锁）
　　Ex--独占锁/排他锁（X锁，Exclusive Lock）
　　Update--更新锁（U锁）
　　Table或者intent--锁发生在表
　　Page--锁发生在表
　　Row--锁发生在行
　　blk--这个进程正在阻塞另一个需要获取一个锁的进程，一旦这个进程处理完成，其他进程就可以继续处理了
　　demand--这个进程正在试图获取一个锁
　　例子-未执行update前：
　　1> sp_lock3x
　　2> go
　　返回结果：

　　1.3触发机制
　　所有关系型数据库都存在行锁，不同数据库触发机制大致相同，只是手工生成行锁方法略有不同。
　　MySQL数据库，不同的存储引擎支持不同的锁机制。MyISAM和MEMORY存储引擎采用的是表级锁；BDB存储引擎采用的是页面锁，但也支持表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。以InnoDB存储引擎举例，意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他行锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享行锁或排他行锁。
　　共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
　　排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
　　Oracle数据库，行锁在操作DML（UPDATE、DELETE、INSERT）语句时，Oracle会自动加上行锁。在select * from table for update 【of column】【nowait|wait 3】时，oracle也会自动加锁；但在缺省情况下，单纯地读数据（SELECT）并不加锁，Oracle通过回滚段来保证用户不读“脏”数据。
　　参数【nowait|wait 3】，一般在for update 时加nowait，这样就不用等待其他事务执行了，一判断有事务，立马抛出错误。
　　参数【of column】，一般的of column都是在多表操作的时候，能锁定其中一个表的某些列，如果还是同一张表的话，并不能锁定某些列。
　　Sybase数据库，支持行锁、页锁、表锁。意向锁是自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，会自动给涉及数据集加排他行锁（X)；对于普通SELECT语句，不会加行锁。
　　2 现象描述
　　测试环境上某系统运行批量程序时，报错“锁资源耗尽”。
　　经分析，产生原因为测试环境上跑批量时，准备清理LSB表中小于当日的数据，Sybase数据库执行SQL语句“EXEC SQL delete FROM LSB WHERE appdate < :Workdate”，数据量约300万条。而当时正在同时发起实时联机交易，对LSB表生成了意向排他锁和排他行锁未释放，该批量执行delete语句需使用300w个行锁（锁资源最大可使用100w个）且因排他行锁存在无法升级使用表锁，导致批量失败，报错“锁资源耗尽”。
　　3 原因分析
　　A：对LSB表执行update语句
　　B：EXEC SQL delete FROM LSB WHERE appdate < :Workdate
　　C：EXEC SQL delete top 4000 FROM LSB
　　WHERE appdate < :Workdate
　　当执行A操作时，数据库对LSB先产生一个意向排他锁，再产生一个排他行锁。当执行B操作时，删除300w条记录，会产生300w条行锁（行锁大于5k条，曾试图升级表锁），但发现LSB表存在意向排他锁，升级行锁失败，只能使用行锁（行锁资源上限为100w条），B交易报错“锁资源耗尽”。
　　另外，测试环境上很少出现“锁资源耗尽”现象，分析原因为意向排他锁是瞬时的、转瞬即逝的，很快就消失，所以非常偶发。只有当A的排他意向锁与B的行锁升级为表锁的时刻碰撞上，才会出现上述现象。
　　当将B方案优化为C方案时，控制每次产生行锁的条数小于5k，避免升级为表锁，这样利于A（前提是A的tps小于500）和C同时操作且互不影响。
　　4 应对措施
　　通过将“流水表批量清理交易”优化为先查找top 1的记录，若记录存在则delete前top 4000条记录，循环执行，直至小于当日的数据全部清理。
　　5 测试验证
　　测试环境的数据库配置参数和铺底数据如下：

　　查看数据库锁资源大小
　　1> sp_configure "number of locks"
　　2> go
　　返回结果：

　　测试发现，当测试环境模拟实时联机交易持续运行（TPS为4-46笔/秒）时，新、旧程序均执行成功，CPU使用率无差异，无死锁，新程序的执行时间长于旧程序。但是，交易背景未复现该问题。
　　当测试环境模拟对LSB表执行update语句、但事务不提交、持续产生排他意向锁和排他行锁的场景后，旧程序运行不到1s，报错“锁资源耗尽”，交易失败，复现该问题。而新程序运行30s，交易成功，锁资源使用数约为4000-4010个。
　　执行SQL语句，但不提交：
　　1> begin transaction
　　2> go
　　1> update PD set workdate = '20200602' where PDid ='23'
　　2> go
　　(1 row affected)
　　1>
　　执行SQL语句，且提交：
　　1> begin transaction
　　2> go
　　1> update PD set workdate = '20200602' where PDid ='23'
　　2> go
　　(1 row affected)
　　1> commit
　　2> go
　　1>
　　因此，当实时联机交易对LSB表生成了排他意向锁未释放时，旧程序复现该问题、交易成功率为0%，新程序执行成功、交易成功率为100%，新程序的清理机制优于旧程序的清理机制。测试通过。

　　WHERE appdate < :Workdate
　　1、交易背景“update语句，但事务不提交，持续产生排他意向锁和排他行锁”下，旧程序报错。
　　2、交易背景“update语句，但事务不提交，持续产生排他意向锁和排他行锁”下，新程序交易成功。
　　6 问题启示
　　程序设计时，需考虑多支交易对数据库（包括MySQL、Oracle、Sybase等）同一张表产生排他行锁是否相互影响的场景。特别是出现删除大量数据等操作，如其他交易也对该表产生排他行锁，建议控制事务大小，如每次的删除记录条数，避免行锁升级表锁失败从而导致交易报错。
　　还需考虑，两个交易均需更新数据库同一张表的记录时，程序均需先锁表，再进行下一步操作，如顺序颠倒易出现交易失败。