MySQL 加锁控制并发的方法

# 添加一个user表
CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(255) NOT NULL COMMENT '姓名',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
# 插入3条数据
INSERT INTO `users` (`id`, `name`)
VALUES
 (1, '雪山飞猪'),
 (2, 'chenqionghe'),
 (3, 'cqh');

UPDATE users SET name="雪山飞猪" WHERE id=3
UPDATE users SET name="chenqionghe" WHERE id=3

ALTER TABLE users ADD `version` INT NOT NULL DEFAULT '0'

UPDATE users SET name="雪山飞猪",version=version+1 WHERE id=3 AND version=0
UPDATE users SET name="chenqionghe",version=version+1 WHERE id=3 AND version=0

LOCK TABLE 表 READ
UNLOCK TABLE;

LOCK TABLE users READ;

SELECT * FROM users WHERE id=1;

UPDATE users SET name="chenqionghe" WHERE id=1

BEGIN;
SELECT * FROM users WHERE id IN (1,2) LOCK IN SHARE MODE
COMMIT;

BEGIN;
SELECT * FROM users WHERE id IN (1,2) LOCK IN SHARE MODE

UPDATE users SET name="雪山飞猪" WHERE id=1

COMMIT;

LOCK TABLE users WRITE;

SELECT * FROM users WHERE id=1

UNLOCK TABLE

BEGIN;
SELECT * FROM users WHERE id IN (1,2) LOCK IN SHARE MODE
COMMIT;

BEGIN;
SELECT * FROM users WHERE id IN (1,2) FOR UPDATE

UPDATE users SET name="chenqionghe" WHERE id=3

UPDATE users SET name="chenqionghe" WHERE id=1

COMMIT;