mysql锁机制

/*
    共享锁：数据可以被多个事务查询，但是不能修改
    创建锁的格式
        SELECT语句 LOCK IN SHARE MODE;
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1数据，并加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99的数据，并加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据,(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1数据,也加入共享锁(共享锁和共享锁是兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1数据，姓名改成张三三(修改失败。会出现锁的情况。只有窗口1提交事务后才能修改成功)
UPDATE student SET NAME=‘张三三‘ WHERE id=1;

-- 修改id为2数据，姓名改成李四四(修改成功，InnoDB引擎默认加的是行锁)
UPDATE student SET NAME=‘李四四‘ WHERE id=2;

-- 修改id为3数据，姓名改成王五五(修改失败，InnoDB引擎如果不采用带索引的列加锁，加的就是表锁)
UPDATE student SET NAME=‘王五五‘ WHERE id=3;


-- 提交事务
COMMIT;

/*
    排他锁：加锁的数据，不能被其他事务加锁查询或修改
    排他锁创建格式
        SELECT语句 FOR UPDATE;
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1数据，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1数据,并加入共享锁(排他锁和共享锁是不兼容的)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1数据，并加入排他锁(排他锁和排他锁是不兼容的)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1数据，将姓名改成张三(修改失败，会出现锁的情况。只有窗口1提交事务后才能修改成功)
UPDATE student SET NAME=‘张三‘ WHERE id=1;

-- 提交事务
COMMIT;

/*
    读锁：所有连接只能读取数据，不能修改
    加锁
        LOCK TABLE 表名 READ;

    解锁
        UNLOCK TABLES;
*/
-- 为product表添加读锁
LOCK TABLE product READ;

-- 查询id为1数据
SELECT * FROM product WHERE id=1;

-- 修改id为1数据，将金额修改4999
UPDATE product SET price = 4999 WHERE id=1;

-- 解锁
UNLOCK TABLES;

-- 查询id为1数据
SELECT * FROM product WHERE id=1;

-- 修改id为1数据，将金额改成5999(修改失败，只有窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;

/*
    写锁：其他连接不能查询和修改数据
    加锁
        LOCK TABLE 表名 WRITE;

    解锁
        UNLOCK TABLES;
*/
-- 为product表添加写锁
LOCK TABLE product WRITE;

-- 查询
SELECT * FROM product;

-- 修改
UPDATE product SET price=1999 WHERE id=2;

-- 解锁
UNLOCK TABLES;

-- 查询(查询失败，只有窗口1解锁后才能查询成功)
SELECT * FROM product;

-- 修改(修改失败，只有窗口1解锁后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;

-- 创建city表
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
    NAME VARCHAR(20),                   -- 城市名称
    VERSION INT                         -- 版本号
);

-- 添加数据
INSERT INTO city VALUES (NULL,‘北京‘,1),(NULL,‘上海‘,1),(NULL,‘广州‘,1),(NULL,‘深圳‘,1);


-- 将北京修改为北京市
-- 1.将北京的版本号读取出来
SELECT VERSION FROM city WHERE NAME=‘北京‘;   -- 1
-- 2.修改北京为北京市，版本号+1.并对比版本号是否相同
UPDATE city SET NAME=‘北京市‘,VERSION=VERSION+1 WHERE NAME=‘北京‘ AND VERSION=1;