MySQL锁机制

锁分类

加锁操作

1. 加全局锁

FLUSH TABLES WITH READ;

2. 加表排它锁

LOCK TABLES student WRITE;

3. 加表共享锁

LOCK TABLES student READ;

4. 释放全局锁和表锁

UNLOCK TABLES;

5. 查看行级锁状态

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM PERFORMANCE_SCHEMA.data_locks;

6. 加行级共享锁，BEGIN 显示开启事务 COMMIT 后释放锁

BEGIN;
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
COMMIT;

7. 加行级排它锁，BEGIN 显示开启事务 COMMIT 后释放锁

BEGIN;
SELECT*FROM student WHERE id=1 FOR UPDATE; 
COMMIT;