search

Home  >  Q&A  >  body text

java - 事务 与 更新丢失的问题?

先查询某一行的值,然后在更新这个值。在高并发的情况下,A 用户 查出来的值比如是 8,这时候按着8进行处理过程中,有另外的用户B,将这个值改成了10,当A用户再去更新的时候,就会造成数据的更新丢失。

通过对查询更新方法设置事务,加入防重复读的隔离级别,也是解决不了更新丢失问题的。防重复读,只能保证第一次读到是8,后面在怎么读这条记录,结果都是8。

解决这个问题,在mysql 数据库层面,只有用for update (悲观锁)或是乐观锁来锁住这一行记录。

问题是,对于事务与mysql悲观锁的理解有点混沌了。请高人给指点迷津。

高洛峰高洛峰2888 days ago547

reply all(4)I'll reply

  • 大家讲道理

    大家讲道理2017-04-18 10:31:12

    Enabling the default isolation level of mysql in the transaction state can already solve this problem.

    reply
    0
  • 阿神

    阿神2017-04-18 10:31:12

    For example
    SET AUTOCOMMIT=0; BEGIN WORK;
    SELECT quantity FROM products WHERE id=3 FOR UPDATE;
    UPDATE products SET quantity = '1' WHERE id=3;
    COMMIT WORK;

    You can turn things on.
    Then add FOR UPDATE when reading this row of records to lock this record
    Using FOR UPDATE must use a transaction, because not using a transaction is similar to not using a FOR UPDATE, and using FOR in a transaction After UPDATE, before COMMIT/ROLLBACK,
    If other sessions read this row with id=3, they will wait forever, wait for your transaction to end, and read new rows
    Secondly, FOR UPDATE is best used with id = xx or id in (xx,xx) otherwise the database will be downgraded to a lock table

    reply
    0
  • PHPz

    PHPz2017-04-18 10:31:12

    WHERE Riga conditions:

    SELECT quantity FROM products WHERE id=3; 假设读到的quantity为8
    UPDATE products SET quantity = '10' WHERE id=3 AND quantity=8;

    reply
    0
  • 大家讲道理

    大家讲道理2017-04-18 10:31:12

    There are 4 levels of transactions
    Read committed, read uncommitted, rereadable, serialized
    Please describe your needs carefully, let me take a look!

    reply
    0
  • Cancelreply