Mysql优化---锁机制、行锁及表锁

文章导读：上文: Mysql优化—分析海量数据

===> 1. 锁的分类

===> 2. 表锁

===> 3. 行锁

===> 4. 行锁的注意事项

---

11.1 锁机制：解决因资源共享 而造成的并发问题

分类：

操作类型：

a. 读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰。
b. 写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他的读锁、写锁。

操作范围：

a. 表锁：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小，加锁快；无死锁；但锁的范围大，容易造成冲突，并发度低。

b. 行锁：一次性对一条数据加锁。如INNODB存储引擎使用行表，开销大，加锁慢；容易出现死锁；锁的范围小，不易发生锁冲突，并发度高（很小的概率发生高并发问题：脏读、幻读）

---

11.2 表锁:

增加锁：lock table 表1 read/write…

查看加锁的表：show open tables

释放锁：unlock tables;

小结：

1、如果给A加了读锁，当前会话只能对A进行读操作。而其他会话对A也可以进行读操作，但写操作需要等待会话释放锁。

2、如果给A加了写锁，当前会话只能对A进行任何操作。而其他会话对A操作需要等待会话释放锁。

3、MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁。

分析表锁定的严重程度：show status like ‘table%’;

• Table_locks_immediate 即可能获取到的锁数
• Table_locks_waited： 需要等待的表锁数（如果该值越大，说明存在越大的锁竞争）

当Table_locks_waited/Table_locks_immediate>1000，建议使用Innodb。

---

11.3 行锁:

注：mysql 默认开启事物提交

为了研究行锁，暂时将自动commit关闭；以下都可以避免事物自动提交

    set autocommit = 0; 

    begin; 

    start transaction;

分析表锁定的严重程度： show status like ‘%innodb_row_lock%’:

   Innodb_row_lock_current_waits：当前·正在等待锁的数量

   Innodb_row_lock_time：等待总时长

   Innodb_row_lock_waits：等待次数、

小结：

1. 如果对话数据对某条数据进行DML操作（默认关闭自动commit），则其他会话必须等待会话结束事物后，才能进行操作。
2. 表锁是通过unlock tables，也可以通过事物提交解锁， 行锁是通过事物提交解锁。
3. 如果其他会话对其他数据进行操作，则互不影响。

---

11.4 行锁的注意事项:

a. 如果没有索引（或索引失效），则行锁会转为表锁。

b. 行锁的特殊情况：间隙锁，值在查询范围内，但却不存在

例如：update table set name =‘xx’ where id>1 and id<9

在此范围内没有id=7的数据，此时Mysql会自动给id=7的数据加间隙锁。

注意：如果存在where，那么行锁则是根据where来判定加锁范围（不是实际的值）

c. 可以通过for update对查询语句进行加锁

例如：select * from table for update