1. Optimistic locking

  • Before updating the data, it is considered that no one will modify the data at the same time. During the update, it is verified whether the data has been modified
  • Implementation method: Add version or updated_AT to the table field, set version+1 after each data update or change updated_AT to the current time, and verify whether the current version value or updated_AT value is consistent with the latest value in the database before the update
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 the 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: 0Copy the code

2. The pessimistic locking

  • There are shared locks and exclusive locks
  • Shared lock: also called read locks, referred to as “S lock, principle: a transaction to obtain a data line Shared lock, and other transactions can obtain the corresponding Shared lock, but cannot obtain the exclusive lock, that is, a transaction when read a data line, other transactions can be read, but not to delete, and update the data line.
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 the 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 the 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 transactionCopy the code
  • Exclusive lock: also called write locks, referred to as “x lock, principle: a transaction to obtain a data row exclusive lock, and other transactions can no longer access to the bank of any locks (exclusive or Shared lock lock), that is, a transaction when read a data line, other affairs not to delete the data line modification and lock the query. Update, INSERT, and DELETE statements are automatically locked.
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 the 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 the 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 transactionCopy the code