MySQL表锁相关

表锁

LOCK TABLES:锁定表，如果表已经被其他线程锁定，则会等待，直到可以获取锁。
UNLOCK TABLES：释放线程持有的全部锁

读与读不互斥；
读与写，写与写互斥。
所以当一个线程获取读锁时，其他线程也可以获取读锁，但不能获取写锁。
当一个线程获取写锁时，其他线程不能获取读锁也不能获取写锁。

表锁使用例子

随便创建一张表
create table stu(
id smallint not null auto_increment,
name varchar(10),
primary key(id))engine=InnoDB;

并插入一些数据
insert into stu(name) values("ZhaZha"),("ZhangMin");

打开两个数据库会话，分别进行下列操作：读与读、读与写、写与写。

读与读

会话A获取读锁: lock table stu read;

然后会话B读取数据:select * from stu;

结果：会话A持有读锁不会对会话B读操作造成影响，读与读可以共用锁。

继续，

读与写

会话B插入一条数据：insert into stu(name) values("TianHang");
由于会话A还持有读锁，所以会话B进行写操作时会进入等待。

让会话A释放锁

会话A释放锁以后，几乎同时，会话B完成了插入。
注意第二行，至此会话2已结等待了6min 23.59sec，不容易。

结果：会话A持有读锁其他会话不能进行写操作，读与写互斥。

写与写

会话A获取写锁: lock table stu write;
注意会话A获取写锁后，会话A是可以进行读操作的，没冲突，没毛病。

然后会话B读取数据:select * from stu;
进入等待…..

(此处我直接Ctrl C了，接下来演示会话B的写操作)

会话B进行写操作：update stu set name="FUHANG" where id =3;
进入等待…

接下来会话A释放写锁：unlock tables;

几乎同时，Truely，会话B完成了更新操作。
注意第二行，会话B等待了5min，不容易。

结果：会话A持有写锁，其他会话不能读，也不能写，读写互斥，写写互斥。

查询表级锁争用情况

show status like 'table%';

table_locks_immediate 指的是立即获得表级锁的次数，就是请求锁没有等待，立刻就成功了。
table_locks_waited指的是不能立即获取表级锁而需要等待的次数。
但显然之前的例子中进行了多次等待，为什么此处没有waited的次数是0？

其原因是5.5新引入的metadata lock（MDL），对表的访问都需要获取MDL。在这个例子中，session 1拥有一个排他MDL，因此Session2是被锁在获取MDL的阶段。
由于MDL是在获取表锁之前，因此在session 2被lock的时，上述两个变量都不变。

[MySQL Q&A] 关于 table_lock_wait 和 table_lock_immediate   http://dinglin.iteye.com/blog/1772644

什么情况下会触发table_locks_waited？
引入MDL以后，table_locks_waited并不容易触发。除非主动lock tables t read。
我们用并发压力，两个线程分别执行update t1 set y=y+1 where id=1;各5000次。
若t1为MyISAM表，MyISAM是表锁，在并发压力下，是会导致table_locks_waited急剧增加。
而在InnoDB表，由于是行锁，因此获取表锁这个逻辑都能顺利通过，因此table_locks_waited不变。

经过实验：
- 会话A执行读锁，会话B进行写操作，table_locks_waited增加
- 会话A执行写锁，会话B进行写操作，table_locks_waited不增加

注意：对于MyISAM表，在执行SELECT前，会自动给表加读锁；在执行UPDATE、DELETE、INSERT前，会给表加写锁。

MyISAM表锁不会出现死锁
因为只有你获取到所需要的所有锁的时候，操作才能正常进行。
而且特别有意思的是：如果会话A通过Lock table语句主动的获取了某个表的锁，那么他将无法对其他表进行操作，因为会话A没有申请其他表的锁。
其实通过select或者其他操作符进行操作时，也都是一次性进行锁申请，完成释放锁。
由于MySQL不支持锁升级，所以对于会话A，只能释放然后重新申请锁。

结论：

• 一个会话获取了读锁，其他会话可以进行读操作，可以获得读锁（执行lock table xx read），但不能获得写锁。
• 一个会话获取了写锁，其他会话不可以读，也不可以写，只能等待。