项目/数据库中的写覆盖问题与解决

最新推荐文章于 2024-08-15 03:53:38 发布

「已注销」

最新推荐文章于 2024-08-15 03:53:38 发布

阅读量3.2k

点赞数 2

默认隔离级别

READ COMMITTED(读提交数据)：允许事务读已提交的数据，但不要求“可重复读”，默认隔离级别并不保证不发现写覆盖。

写覆盖问题

两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失

如：

银行系统中的一个活动序列:

甲事务读出acct_id为‘A1234’的余额(balance) A,设A=16.

乙事务读出acct_id为‘A1234’的余额(balance) A,设A=16.

甲事务修改余额A←A+10.所以A为26,把A写回数据库.

乙事务也修改余额A←A+10.所以A为26,把A写回数据库.

结果明明增加了20，数据库中余额只为26。

显然，这是不合理的。

在默认隔离级别下解决写覆盖问题一般有下面的方向：

1，避免先读取再执行写操作的事务设计

2，在事务一开始就对数据加独占锁

三种封锁协议

首先，应对数据库的并发操作，一般有三种的封锁协议

1级封锁协议

事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。

1级封锁协议可防止丢失修改，并保证事务T是可恢复的。

在1级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不读“脏”数据。

2级封锁协议

1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。

2级封锁协议可以防止丢失修改和读“脏”数据。

在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。

3级封锁协议

1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。

3级封锁协议可防止丢失修改、不读脏数据和不可重复读。

写覆盖问题解决方案

1，使用原子性的事务来代替“先读取再执行写操作”

exec sql update accounts set balance=balance +10 where branch_id=‘SFBay’;

exec sql commit work;

因为每一个SQL语句都是原子性的，所以更新时，不会出现写覆盖问题。balance也没有过时的问题。

2，让先读取后执行变成“原子操作”

采用2级封锁协议

事务甲正在修改某些记录，它可能是采用封锁的方法锁住这些记录，事务乙试图读这些记录，则它必须等到事务甲释放封锁。为了保证乙不读脏数据，事务甲应该在提交或回滚时释放这些封锁。对于要读的记录，事务（无论是甲还是乙）都应该采用读封锁，对于要修改或删除的记录，事务都应该采用写封锁。读锁可以在读完该记录后立即释放，写锁应该在事务提交或回滚时才释放。

例如下面的程序可以防止写覆盖：

exec sql declare cursor deposit for select balance from accounts

where branch_id=‘SFBay’ for update of balance;

Exec sql open c;

(now loop through rows in coursor. And for each pass do)

exec sql fetch c into :balance;

balance=balance +10;

exec sql update account set balance=:balance where current of deposit;

(end of loop)

exec sql close deposit;

exec sql commit work;

这段程序借助于游标稳定性来运行，游标定位在某一行上，就会锁定该游标所引用的这一行。在FETCH对balance记录加了共享锁，UPDATE对balance记录加了排它锁，保证balance没有被其它的事务篡改，balance不会过时，这个更新就类似于原子性的更新了。

而下面的程序没有防止写覆盖：

exec sql select balance into :balance from accounts where acct_id=‘A1234’;

balance=balance +10;

exec sql update accounts set balance=:balance where acct_id=‘A1234’

exec sql commit work;

因为在执行update之前，balance可能已经被篡改了。如果balance过时，就会可能发生写覆盖问题。

3，事务一开始就加独占锁

这是肯定可以解决写覆盖问题，因为在封锁数据时，别的事务不能更新数据。直到事务完成后，别的事务才可以获得锁。

常常在项目中见到这样的DML语句:

UPDATE table1 set column1=2 where id=12345

这么简单的语句，有什么稀奇的地方呢？我来模拟一个场景，用户A在页面上查询到这条ID=12345的记录，他看到column1的值是1，他想更新到2 。于是他点击了更新，同时用户B也看到了这条记录，他看到这条记录的column1的值也是1，他想更新到3，于是他也点击更新。这个时候问题来了，由于用户A心里想的是2，这个时候他更新完毕之后，查询出来看到的值却是3.这时估计用户A感觉灵异事件发生了...

上面是一个很简单的场景，而且在项目中也经常遇到，这就是数据写覆盖问题。刚出来工作的同学最容易犯这个问题，我这里提供几个方法权当供大家参考：
①利用语句级SQL执行的原子性
    假设一个总额字段 total初始值是20，如果以后每次总额增加10元，我们就要更改这个字段。
    容易犯的错误：
    首先，

select total from table1 where id=12345

然后在程序中

var temp=total+10

最后一步

UPDATE table1 set total=#temp# where id=12345

解决方案：

UPDATE table1 set total=total+10 where id=12345

   这里直接利用语句级的原子性，不用先查询再做加法。

②悲观锁：利用for update行锁（先查询再更新）
   同样是上面的例子。
   解决方案：
    首先，开启事务

select total from table1 where id=12345 for update

然后在程序中

var temp=total+10

之后更新

UPDATE table1 set total=#temp# where id=12345

   最后，关闭事务。
   注意，这里几条基于一定要在事务中执行。

③乐观锁：更新时检查版本标志
    新增一个字段version,每次update都要对这个version加1，更新的时候检查这个version是否变化，如果已经变化了，表示被更新过了。
    首先

select total,version,id from table1 where id=12345

假设这里version=20,
然后在程序中

var temp=total+10

之后更新

 
  UPDATE table1 set total=#temp#,version=version+1 where id=12345 and version=20
 

检查更新语句的返回值是否等于1,如果不等于1表示更新不成功，根据实际业务情况，可以直接反馈给用户，或者再做其他重试处理。

上面三种方法一般都有自己的使用场合：
①第一种方法，一般适合某个字段需要根据更新前的状态动态增加或者减少等做出变化的情况，这类需求处理起来无往不利。
② 第二种方法适用于用户更新一定要保证成功的情况，即更改及有效的情况，如果并发更新程序很高，一般采用这种方式比较妥当，可以减少更新失败的情况。
③第三种方法，不需要对数据进行事先锁定，更新只需要检查版本标志，即可知道是否已经被别人更新过了，适合于对更新成功要求不高的用户，即可以通过简单提示，然后让用户重新查询再修改的情况。如果并发程度很高，这种方法失败的几率也很高，所以一般不太适合。

当然，这些都要根据实际情况来决定，一般系统中会综合运用上诉的几种方法来解决问题。总之一句话，写覆盖的问题大家一定要引起重视，不然出现了问题，你也只能按灵异事件处理了，因为并发问题，有的数据怎么来的都不知道。

早先我也曾提到过这里的问题：《保持业务数据同步》

平时数据库处理时，总要接触关于数据同步修改的问题，有时候我们需要在业务处理时保证业务数据同步(一般这种情况都包含统计字段，也就是说需要根据前面的值算出后面的值的字段)，例如，两个人同时查询出1条记录后，先后对其加1，保存入数据库中，这样就可能造成保存问题，因此，需要对该数据进行同步。这里有3种方法可以采用：

1.增加一个版本字段，查询时得到该字段，修改后增加1，以后其他的修改需要进行比较，如果不同，则保存失败（需要额外比较）

2.依赖数据库本身的机制，对将要修改的数据进行锁定（该方法会造成数据库访问瓶颈）

3.在保存时使用复合sql语句进行修改，例如 updata a tem1 set tem1.filed=(select tem2.filed+1 from a tem2 where tem1.id=tem2.id ) where tem1.id=1;