MySQL高级——MySql锁机制（表锁）

一、MySql表锁(偏读)特点

• 偏向MyISAM存储引擎；
• 开销小，加锁快；
• 无死锁；
• 锁定粒度大，发生锁冲突的概率最高,并发度最低。

二、建表SQL

• 创建mylock表的SQL

  CREATE TABLE mylock (
    id INT(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MYISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
  
  INSERT INTO mylock(username) VALUES('a');
  INSERT INTO mylock(username) VALUES('b');
  INSERT INTO mylock(username) VALUES('c');
  INSERT INTO mylock(username) VALUES('d');
  INSERT INTO mylock(username) VALUES('e');
  

  

• 创建book表的SQL

  CREATE TABLE book (
    id INT(11) NOT NULL AUTO_INCREMENT,
    bookname VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MYISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
  
  INSERT INTO book(bookname) VALUES('java');
  INSERT INTO book(bookname) VALUES('c++');
  INSERT INTO book(bookname) VALUES('.net');
  INSERT INTO book(bookname) VALUES('python');
  INSERT INTO book(bookname) VALUES('vue');
  

  

三、MySql手动增加表锁的案例分析

1、查看表是否加过的锁

SHOW OPEN TABLES;

2、手动增加表锁语法

 lock table 表名字1 read(write)，表名字2 read(write);

3、手动给mylock表增加读锁，book表增加写锁

lock table mylock read,book write;

4、再次查看表是否加过的锁

SHOW OPEN TABLES;

四、MySql手动释放表锁的案例分析

1、手动释放表锁语法

unlock tables;

2、释放给表增加的锁

unlock tables;

3、再次查看表是否加过的锁

SHOW OPEN TABLES;

五、MySql手动增加表的（读锁）案例分析

1、在（客户端1）中，手动增加mylock表的读锁

lock table mylock read;

2、在（客户端2）中是否能查询到（客户端1）中上锁的mylock表数据。如下图：

• 结论：在（客户端1）中手动增加mylock表的读锁，（客户端1）能查看到mylock表的数据，（客户端2）也能查看到mylock表的数据。

3、在（客户端1）中，是否能手动修改mylock表的数据，如下图：

• 结论：在（客户端1）中手动修改mylock表的数据，会提示mylock表增加了表的读锁，因此不能被修改。

4、在（客户端1）中，是否能查询除mylock表以外的其他表数据，如下图：

• 结论：在（客户端1）中不能查询除mylock表以外的其他表数据。

5、在（客户端2）中，是否能修改（客户端1）锁定的mylock表数据如下图：

• 结论：在（客户端2）中，不能修改（客户端1）锁定的mylock表数据，会一直等待获得锁。

6、在（客户端1）中，释放mylock表锁，然后查看（客户端2）中，修改的数据耗时。如下图：

六、MySql手动增加表的（写锁）案例分析

1、在（客户端1）中，手动增加mylock表的写锁

LOCK TABLE mylock WRITE;

2、在（客户端1）中，是否能修改mylock表的数据。

• 结论：在（客户端1）中，能修改（客户端1）锁定的mylock表数据。

3、在（客户端1）中，是否能查询其他表（即book表）的数据。

• 结论：在（客户端1）中，不能查询（客户端1）锁定的mylock表数据。

4、在（客户端2）中，是否查询mylock表的数据。

• 结论：在（客户端2）中，不能查询（客户端1）锁定的mylock表数据。会一直等待获取mylock表的锁。

4、在（客户端1）释放锁之后，（客户端2）是否可以查询到mylock表的数据。

• 结论：在（客户端1）释放锁之后，（客户端2）可以查询到mylock表的数据。

七、MySql锁机制（表锁）的案例总结

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

1、MySQL的表级锁有两种模式

• 表共享读锁（Table Read Lock）
• 表独占写锁（Table Write Lock）

2、结论

• 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
• 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
• 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

八、 MySql表锁分析

1、查看哪些表被加锁的命令

show open tables;

2、如何分析表锁定

• 可以通过检查table_locks_waited 和table_locks_immediate状态变量来分析系统上的表锁锁定；
• 命令 show status like ‘table%’;
  
• 这里有两个状态变量记录Mysql内部表级锁定的情况，两个变量说明如下：
• Table_locks_immediate ：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
• Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。
• 此外，Myisam的读写锁调用度是写优先，这也是myisam不合适做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量更新会使查询很难得到锁，从而造成永远阻塞。