Mysql 乐观锁与悲观锁

608 阅读3分钟

1.乐观锁

  • 在更新数据前认为不会有人同时修改该数据,在更新时再去校验数据是否被修改过
  • 实现方式: 通过给表字段增加version或者updated_at,每次更新数据后给version+1或者修改updated_at为当前时间, 在更新前去校验当前version值或updated_at是否与数据库中的最新值一致
 session1:
 MariaDB [blog]> select * from articles;
 +----+-------+---------+---------------------+---------------------+
 | id | title | content | created_at          | updated_at          |
 +----+-------+---------+---------------------+---------------------+
 |  2 | zz    | zzz     | 0000-00-00 00:00:00 | 2018-05-06 16:59:03 |
 |  9 | as    | NULL    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
 +----+-------+---------+---------------------+---------------------+
 2 rows in set (0.00 sec)
 ​
 -----------------------------------------------------------------------------
 ​
 session2:
 MariaDB [blog]> update articles set title = "cc", updated_at = NOW() where id = 2;
 Query OK, 1 row affected (17.17 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 ​
 -----------------------------------------------------------------------------
 ​
 session1:
 MariaDB [blog]> update articles set title = "xx" where id = 2 and updated_at = "2018-05-06 16:59:03";
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 0  Changed: 0  Warnings: 0

2. 悲观锁

  • 分为共享锁和排它锁
  • 共享锁:也叫读锁,简称S锁,原理:一个事务获取了一个数据行的共享锁,其他事务能获得该行对应的共享锁,但不能获得排他锁,即一个事务在读取一个数据行的时候,其他事务也可以读,但不能对该数据行进行删除和更新。
  session1:
 MariaDB [blog]> begin;
 Query OK, 0 rows affected (0.00 sec)
 ​
 MariaDB [blog]> select * from articles where id = 2 lock in share mode;
 +----+-------+---------+---------------------+---------------------+
 | id | title | content | created_at          | updated_at          |
 +----+-------+---------+---------------------+---------------------+
 |  2 | cc    | zzz     | 0000-00-00 00:00:00 | 2018-05-06 17:00:28 |
 +----+-------+---------+---------------------+---------------------+
 1 row in set (0.00 sec)
 ​
 -----------------------------------------------------------------------------
 ​
 session2:
 MariaDB [blog]> select * from articles where id = 2;
 +----+-------+---------+---------------------+---------------------+
 | id | title | content | created_at          | updated_at          |
 +----+-------+---------+---------------------+---------------------+
 |  2 | cc    | zzz     | 0000-00-00 00:00:00 | 2018-05-06 17:00:28 |
 +----+-------+---------+---------------------+---------------------+
 1 row in set (0.00 sec)
 ​
 MariaDB [blog]> update articles set title = "zz" where id = 2;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 MariaDB [blog]> insert into articles(title) values("ss");
 Query OK, 1 row affected, 2 warnings (0.00 sec)
 ​
 MariaDB [blog]> delete from articles where id = 2;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  • 排它锁:也叫写锁,简称x锁,原理:一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的任何锁(排他锁或者共享锁),即一个事务在读取一个数据行的时候,其他事务不能对该数据行进行删除修改和加锁查询。对于update,insert,delete语句会自动加排它锁。
 session1: 
 MariaDB [blog]> begin;
 Query OK, 0 rows affected (0.00 sec)
 ​
 MariaDB [blog]> select * from articles where id = 2 for update;
 +----+-------+---------+---------------------+---------------------+
 | id | title | content | created_at          | updated_at          |
 +----+-------+---------+---------------------+---------------------+
 |  2 | cc    | zzz     | 0000-00-00 00:00:00 | 2018-05-06 17:00:28 |
 +----+-------+---------+---------------------+---------------------+
 1 row in set (0.00 sec)
 ​
 -----------------------------------------------------------------------------
 ​
 session2:
 MariaDB [blog]> begin;
 Query OK, 0 rows affected (0.00 sec)
 ​
 MariaDB [blog]> select * from articles where id = 2;
 +----+-------+---------+---------------------+---------------------+
 | id | title | content | created_at          | updated_at          |
 +----+-------+---------+---------------------+---------------------+
 |  2 | cc    | zzz     | 0000-00-00 00:00:00 | 2018-05-06 17:00:28 |
 +----+-------+---------+---------------------+---------------------+
 1 row in set (0.00 sec)
 ​
 MariaDB [blog]> select * from articles where id = 2 for update;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 MariaDB [blog]> delete from articles where id = 2;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 MariaDB [blog]> select * from articles where id = 2 lock in share mode;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction