mysql使用内部锁和外部锁，而5.5.3又引入了元数据锁

外部锁

仅用于MyISAM表，当表可能由多个mysql服务器或者myisamchk在线访问时，用以协同访问；

当服务器激活外部锁时，进程访问表之前需要申请文件系统锁，如果无法获取则等待；

可任意时刻使用myisamchk进行读操作，当进行诸如修复或优化表等写操作时，还是要确保mysqld此刻没有用到这个表，否则同时访问表可能会造成其损坏；

可使用repair/optimize/check table替代myisamchk；

参数skip_external_locking用于控制是否激活外部锁，默认不激活

元数据锁

执行事务时基表会被添加metadata lock，以阻止其他会话对该表进行DDL；

5.5.3之前，一旦语句执行完就会释放该锁，但若此时其他事务对基表执行了DDL，则二进制日志的顺序可能会乱掉，故5.5.3起只在事务结束后才释放metadata lock；

倘若获取metadata lock的语句解析成功但执行失败，依旧不会提前释放此锁，只因该失败的sql会被写入二进制日志，需要锁保护日志一致性；

元数据锁在表锁之前获取，如果有会话采用lock table write锁住了表(获取了排他元数据锁)，则后续试图访问该表的其他会话被阻塞于元数据锁等待，而table_locks_waited不会增加；

内部锁

根据存储引擎，可划分为表锁和行级锁；

对于MyISAM/Memory/Merge，使用表锁，而innodb使用行级锁；

下面只介绍InnoDB相关锁

行锁分为共享锁s和排他锁x；此外还有gap锁，两者一起组成了next-key锁，主要用于保证repeatable read；

行锁提供良好的并非性，但是相对表锁，有一定的额外开销

意向锁

innodb支持多粒度锁，允许行锁和表锁共存，即意向锁；意向锁就是InnoDB的表级锁，在一个事务中揭示下一行将被请求的锁类型，包括2种类型：

意向共享锁IS lock：事务想获取一个表中某几行的共享锁，select … lock in share mode

意向排他锁IX lock：事务想获取一个表中某几行的排他锁，select … for update

select for update/lock in share mode会采用read-commited的事务级别，即读取最新的已提交行，insert select也是如此；

意向锁除了全表请求，诸如lock tables … write外不阻塞其他操作

意向锁和行锁兼容关系，如下表所示

表锁

对于Innodb表，若要使用表锁，必须先设置autocommit=0，且在事务提交后再unlock tables，如下例所示：

SET autocommit=0;

LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...

COMMIT;

UNLOCK TABLES

调用lock tables时，除了mysql层的表锁，还需要获取innodb表锁；commit时innodb释放内部表锁，unlock tables时mysql服务器才释放表锁；

如果autocommit=1，innodb不会获取内部表锁，极易导致死锁发生；

相关参数

innodb使用mutex实现锁，mutex_enter申请锁，mutex_exit释放，在mutex_exit前后续mutex_enter会被阻塞

innodb_lock_wait_timeout用于控制等待时间，默认50秒，动态参数

innodb_rollback_on_timeout超时后是否对事务回滚，默认off只撤销失败sql，静态参数

外键与锁

innodb为自动为外键约束加锁，当对外键值进行DML时，会以select ... lock in share mode方式对父表记录添加s锁

ITPub博客

mysql的锁机制