高性能MySQL(锁的调试 附录E)

服务器级别的锁

• 表锁

表可以被显示的读锁和写锁锁定，查询过程中也有隐式的锁。在MySQL会话中使用LOCK TABLES命令可以显示加锁，当一个线程持有锁后，其他线程会等待阻塞，这时使用的是MySQL服务器中的锁，而不是存储引擎的:

mysql> lock tables film read; #第一个连接
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables film write; #第二个连接会挂起等待不会立即完成
Query OK, 0 rows affected (1 min 49.04 sec)

mysql> show processlist\G #显示第二个连接id=40的线程是waiting状态
*************************** 1. row ***************************
     Id: 39
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 2. row ***************************
     Id: 40
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 103
  State: Waiting for table metadata lock
   Info: lock tables film write
2 rows in set (0.00 sec)

显示锁并不是只阻塞显示锁，服务器在查询的时候有时会隐式地锁住表，这时候尝试LOCK 也会阻塞，因为此时表已经隐式地加锁了:

mysql> select sleep(30) from film limit 1; #sleep 休眠

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 40
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 2. row ***************************
     Id: 41
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 17
  State: Waiting for table metadata lock
   Info: select sleep(30) from film limit 1
2 rows in set (0.00 sec)

• 全局锁

通过 FLUSH TABLES WITH READ LOCK 或者设置 read_only=1来获取单个全局锁。它与任何表锁冲突。这也是MySQL服务器实现的一个锁，下面是一个全局读锁的实例:

mysql> flush table with read lock; #获取一个全局读锁
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables film write; #尝试显示上锁

mysql> show processlist \G #state可以看到正在等待全局读锁
*************************** 1. row ***************************
     Id: 40
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 15
  State: Waiting for global read lock
   Info: lock tables film write
*************************** 2. row ***************************
     Id: 43
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 0
  State: init
   Info: show processlist
2 rows in set (0.00 sec)

• 命名锁

命名锁是表锁的一种，服务器在重命名或创建一个表时创建，与普通的表锁相冲突，无论是显示还是隐式。

• 用户锁

在服务器中实现的一种锁，一个基本的命名互斥量。创建锁时需要制定锁的字符串和超时秒数，如下面实例30秒超时的用户锁，其中一个线程创建用户锁后，另一个线程创建同名用户锁会阻塞30秒:

mysql> select get_lock('mylock',30);
+-----------------------+
| get_lock('mylock',30) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select get_lock('mylock',30);
+-----------------------+
| get_lock('mylock',30) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (30.01 sec)

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 40
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 5
  State: User lock
   Info: select get_lock('mylock',30)
*************************** 2. row ***************************
     Id: 43
   User: root
   Host: localhost
     db: sakila
Command: Query
   Time: 0
  State: init
   Info: show processlist
2 rows in set (0.00 sec)

InnoDB中的锁

InnoDB在show innodb status 中输出一些锁信息。如下开启一个事务，并显示加上写锁，此时另一个会话执行相同操作会阻塞:

mysql> begin; #开启一个事务 加上行级排它锁
Query OK, 0 rows affected (0.00 sec)
mysql> select film_id from film limit 1 for update;

mysql> begin; #此时另一个事务会超时等待，只有前一个事务commit提交释放锁才能执行成功
Query OK, 0 rows affected (0.00 sec)

mysql> select film_id from film limit 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

使用information_schema表

这里可以看到InnoDB的事务和锁。