04_mysql锁优化

1. MyISAM表锁（不支持事务）

1.1 如何加表锁

• myisam默认在查询语句前给涉及的表自动加读锁，在增删改操作前自动给涉及的表加写锁； 执行操作完会立即释放
• 显示加锁语法； 执行操作完需要手动释放

# 加读锁
lock tables tb_book read;

# 加写锁
lock tables tb_book write;

# 释放锁
unlock tables;

1.2 特点

• 读锁不会阻塞其他用户对同一表的读操作，但会阻塞对同一表的写操作
• 写锁会阻塞其他用户对同一表的读和写操作

1.3 查看表级锁的相关信息的命令

show status like 'Table_locks%';

2. InnoDB的行锁模式（支持事务）

2.1 如何加行锁

• 对于增删改语句，InnoDB会自动给涉及到数据集行加排他锁（X）

• 对于普通的select语句，InnoDB不会加任何锁

• 可以通过以下语句给select涉及的数据行加共享锁或排他锁

  # 共享锁 S
  select * from tb_user where id>10 lock in share mode;
  
  # 排他锁 X
  select * from tb_user where id>10 for update;
  

• 在事务提交时会自动释放加的所有锁

2.2 InnoDB的锁优化建议

• 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁
• 合理涉及索引，尽量缩小锁的范围
• 尽可能检索索引条件，及索引范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 满足业务需求的同时尽可能使用低级别事务隔离

2.3 查看innodb行锁争用情况

show status like 'innodb_row_lock%';