How to apply pessimistic locking and optimistic locking in Mysql

1. Lock

In life: Locks are everywhere around us. For example, when I go out to play, I need to lock the door. For example, when I need to put money in a safe, I must lock it to ensure Security of my property.

In the code: For example, if multiple threads need to operate and modify shared variables at the same time, you need to lock the variable (syncronized) to ensure that the variable value is correct.

Database table: When multiple users modify the same data in the table, we can lock the row data (row lock).

sql script

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `deleted` tinyint(1) DEFAULT NULL COMMENT '是否删除',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_id` bigint(20) DEFAULT NULL COMMENT '创建人',
  `update_id` bigint(20) DEFAULT NULL COMMENT '操作人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `status` varchar(255) DEFAULT NULL COMMENT '状态',
  `dog` text DEFAULT NULL COMMENT '狗',
  `version` int(11) DEFAULT NULL COMMENT '版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `sys_user`(`id`, `name`, `age`, `email`, `deleted`, `create_time`, `create_id`, `update_id`, `update_time`, `status`, `dog`, `version`) VALUES (1, 'gukong', 19, 'test1@baomidou.com', 0, NULL, NULL, NULL, NULL, NULL, NULL, 0);

2. Pessimistic lock

When we want to modify a piece of data in the database, in order to avoid being modified by others at the same time, it is best to The solution is to directly lock the data to prevent concurrency.

Pessimistic lock is called pessimistic lock because it adopts a pessimistic concurrency control method, assuming that the data will be modified by other threads, so a locking operation will be performed when the data is modified. We generally believe that the probability of data being modified concurrently is relatively high, so it needs to be locked before modification.

Row locks, table locks, read locks, write locks, and syncronized locks in the database are all pessimistic locks.

mysql turns on pessimistic locking, example sql statement

--开启事务
BEGIN
--对行加锁
SELECT * FROM `sys_user` where id = 1 for UPDATE
--修改加锁行的数据
update sys_user set name = 'gukong',age = 18 where id = 1;
--提交事务
commit;

--开启事务
BEGIN
--对行加锁
SELECT * FROM `sys_user` where id = 1 for UPDATE
--修改加锁行的数据
update sys_user set name = 'kulilin',age = 20 where id = 1;
--提交事务
commit;
update sys_user set name = 'kulilin',age = 20 where id = 1;

3. Optimistic lock

Optimistic locking is to maintain optimism about data conflicts Attitude, when operating data, the operated data will not be locked. Only when the data is submitted, a mechanism will be used to verify whether there is a conflict in the data.

Optimistic locking is usually implemented by adding a version (version) or timestamp (timestamp) to the table. Among them, version is the most commonly used.

Optimistic lock will bring a version number every time it performs a data modification operation. Once the version number is consistent with the data version number, it can perform the modification operation and perform 1 operation on the version number, otherwise it will execute fail.

Optimistic lock example:

-- version = 0
SELECT * FROM `sys_user` where id = 1 
update sys_user set name='小明',version = version+1 and age = 20 where id = 1 and version = 0;
-- version = 0 ，而此时version=1,更新失败
SELECT * FROM `sys_user` where id = 1 
update sys_user set name = '小红' version = version+1 and age = 25 where id = 1 and version = 0;

4. How to choose

Optimistic lock is suitable for scenarios with more reading and less writing, and can be omitted The overhead of frequently locking and releasing locks improves throughput

When frequent write operations are required, using optimistic locks may produce a large amount of spins, consume CPU, and affect performance. The reason is that the version is inconsistent and retries are continued. renew. In this case, pessimistic locking is suitable

The above is the detailed content of How to apply pessimistic locking and optimistic locking in Mysql. For more information, please follow other related articles on the PHP Chinese website!