1 T a b l e l o c k s
lock tables table_name write;
lock tables table_name read; 显示给 table_name 加读写的表锁
unlock tables ;
显示释放锁
select sleep(30) from table_name 则会给 table_name 加隐式锁
1 . 2 T h e G l o b a l R e a d l o c k
Flush tables with read lock;
该语句会加 global read lock 锁。如果此时再执行 lock tables table_name write
的话，则该 SQL 语句会等待 global read lock 释放。
1 . 3 N a m e L o c k s
当 MySQL rename/drop 一个表的时候会获取 name locks。如果之前已经使用显示/隐 式获得表锁, 当 rename/drop 时，rename/drop 语句会等待表锁释放。
eg: lock tables table_name write;
rename table table_name to table_new_name;
1.4 U s e r L o c k s
通过 get_lock()函数获取用户自定义锁。
eg:
select get_lock('my_lock', 100);
该语句会获取名字为 my_lock 的锁，获取锁的超时时间为 100s,
如果此后另外一个 session 执行同样的语句的话，则该 session 会锁等待。
2 i n n o d b 的 锁
2 . 1 简 介
innodb 实现了行锁机制，这是它相对 myisam 的最大优点之一。但 是 它 的 行 锁 是 一 种 基 于 索 引 的 “ 准 行 锁 ” ，也就是说，只 有 通 过 索 引 检 索 数 据 才 会 加 行 锁 ， 否 则 加 的 将 是 表 锁 。而 且 对 于 相 同 索 引 的 不 同 记 录 ， i n n o d b 加 的 是 同 一 个 锁 。
例如: select * from t where name = “iknow” for update;
只有当你在 name 字段建了索引，innodb 才会给以上语句加行锁， 并且如果name=”iknow”有多条记录， innodb 会给这多条记录一起加行锁。
另外，需要注意的是，在不同索引上的锁也会互斥,也就是说，如 果 两 条 更 新 语 句使 用 了 不 同 的 索 引 ， 但 是 这 两 个 索 引 覆 盖 了 相 同 的 记 录 ， 那 么 它 们 会 也 会 互 斥 。 由于 innodb 采用了 mvcc 的事务机制，所以它的锁只会阻塞写，从不阻塞读。
在对索引加锁的时候，innodb 根据检索条件的不同会采取不同粒度的锁，包括 record lock，gap lock 和 next-key lock。此外，innodb 也提供了两种显式表锁，帮助应 用层保证数据一致性。
对于不同的 sql 语句在不同的事务级别中，innodb 加的锁是不一样的。
2.2 隐 式 锁
2 . 2 . 1 r e c o r d l o c k
record lock 是对单个索引记录上的锁。 一般更新语句涉及相等条件检索的时候会加该锁。 例如:
select * from table where key = 100 for update
这条 SQL 加的是 record lock，只锁住 key = 100 这一条记录。
2 . 2 . 2 g a p l o c k
gap lock 是指加在索引记录之间的缝隙、第一条记录前或最后一条记录后的锁。 一般当更新设计范围条件查询时加该锁。
例如:
select * from table where key > 100 for update
由于是一个范围检索，所以加的是 gap lock，将锁住所有 key > 100 的记录。
2 . 2 . 3 n e x t - k e y l o c k
next-key lock 是 record lock 和 gap lock 的统称。
2 . 3 显 式 锁
显示锁只有当 set autocommit = 0 或者 start transaction 的时候才会有效。
2 . 3 . 1 显 式 共 享 锁
SELECT ... LOCK IN SHARE MODE
此模式下，当前事务将获得对所有行的 S 锁，其他事务试图更新这些行时会阻塞。
2 . 3 . 2 显 式 排 它 锁
SELECT ... LOCK FOR UPDATE。
此模式下，当前事务将获得对所有行的 X 锁，其他事务试图获得 X 锁时将会阻塞。
2.4 i n n o d b 死 锁
2 . 4 . 1 简 介
innodb 引入了比较灵活的加锁机制，应用层可以自己控制加锁顺序，但因此也带来 了死锁的问题。例如：EB 的死锁 case 和 mysql status 总结文档都讲述了死锁的实际例子。
在监测到死锁时，innodb 的处理策略是，选择其中的一个事务让它继续执行，并杀 掉其它事务。但是 innodb 并不能保证监测到所有死锁，此时当锁等待超过 innodb_lock_wait_timeout 时，innodb 会判断为死锁。因此 innodb 不会像 myisam 那样让 线程陷入无限等待状态。
为了彻底避免死锁，我们可以采用序列化更新。对于 cm 模式，更新天然就是序列化 的。对于主从模式，一个可能的方法是在数据库里创建一个 mutex 表，每个线程更新之前先 锁定该表，从而达到序列化更新的目的。
但是，很多情况下由于应用的需求所限，我们难以做到序列化更新，因此我们的程序 需要遵守一定原则，尽量避开死锁。
2 . 4 . 2 T i p s
- 建议程序访问一组表时，尽可能按固定顺序访问表，对一个表而言，尽可能以固定顺序 存取表中的行。
- 建议事务尽可能小粒度并且及时提交，这样可以尽早释放该事务获取的锁。因为在事务 里通过”select xxx from table t for update” 这种语句获取的锁， 是在事务 commit 时一起释放的，因此如果事务执行时间很长，就有可能造成其他线程阻塞。
- 建议在程序里适当考虑死锁带来的事务执行失败。有些可能简单的丢弃该事务即可，但 有些应用可能需要重启该事务
2.5 i n n o d b 锁 T i p s
- 建议涉及更新的检索条件尽可能使用索引。
例如 update t set value=xx where (conditon)，那么 where 语句最好使用索引。这 是因为，innodb 的行锁是基于索引的，如果检索条件里不包含索引，那么将触发它的表 锁，有可能造成更新效率的严重下降。
- 在表设计时需要考虑索引的区分度，以免造成锁粒度太大。innodb 对于相同索引上的记 录将加上同一个行锁。如果区分度不够，那么可能一条更新语句就会锁住许多行记录。
- 建议通过 explain 确定你的更新语句不会触发表锁。这是因为，即使查询条件里包含了 索引字段，但是如果 mysql 分析执行代价时认为全表扫描效率更高，那么它执行时就不 会用到索引，这种情况下 innodb 将会使用表锁。可以建议通过查看 expalin 的 key 字段 是否为空，确认是否 mysql 执行过程中使用了索引。
- 建议设计好你的 sql 语句，尽可能触发最小粒度的锁级别。如有可能，用相等条件取代 范围条件进行检索，避免触发 innodb 的 next-key 锁。需要注意的是，即使是使用相等 条件检索，如果该条件下的记录为空，innodb 也会使用 next-key 锁。
- 注意如果表里有一个 auto_increment 字段，那么 insert 操作将会加表锁。这是因为， innodb 会在内存里保存一个计数器用来记录 auto_increment 的值，需要对该值进行互 斥。如果这确实对于你的性能影响很大，那么可以通过 innodb_autoinc_lock_mode（5.1.22 版本后引入）调整该加锁的行为，但是这样可能会 导致在主从复制下的数据不一致。一般情况下建议不要修改该值。
- 行锁不是万能的，某些场合也可以考虑表锁。如果事务里需要更新表里的大部分数据， 那么行锁的效率是比较低的，有可能造成线程切换频繁或其它事务锁等待时间太长。此 时应该使用表锁。
- 如果在你的事务里调用用了 lock table，那么必须在事务 commit 后再 unlock，这是因为 unlock 语句会隐式提交事务。正确流程如下所示：
1.start transaction
2.lock your table
3.do some thing
4.commit
5.unlock your table
3 <FONT face=""">M y i s a m 的 锁
3 . 1 简 介
myisam 只有表锁，可以分成共享锁（S 锁）和排它锁（X 锁）两种类型。其中 S 锁之 间可以并发，但是 S 锁和 X 锁，以及 X 锁之间是互斥的。此外对于 insert 操作 myisam 做了 特殊处理，当 concurent_ insert=1 的时候，select 操作和尾部追加的 insert 操作可以并 行进行。当你在使用 load data infile 导数据时尤为重要。
正是由于 myisam 的表锁机制，所以对于需要大量更新的场合，mysiam 的查询效率会 严重下降，因此 myisam 只适合于更新相对较少，查询为主的应用。Myisam 的加锁是默认进 行的，在执行查询语句的之前，myisam 会自动对涉及的表加 S 锁，更新语句会自动加 X 锁。 Myisam 的锁默认是写优先的，如果写请求比较多，可能会造成读请求的阻塞。可通过set low_priority_updates=1 降低该连接的更新请求的优先级。此时所有 INSERT、UPDATE、DELETE 和 LOCK TABLE WRITE 语句等待，直到在该表上的 SELECT 或 LOCK TABLE READ 执行完毕。也可以通过指定启动参数 low-priority-updates，使 myisam 引擎默 认给与读请求优先权利。或者通过 max_write_lock_count 调节 myisam 的锁行为。或者在 update 等更新语句也可以通过 low_priority 属性指定优先级。
Myisam 中，默认情况下，对尾部追加的 insert 操作是不会被 select * from table t for update hang 住的。
3.2 T i p s
- 当使用 lock tables 时，不仅需要一次锁定用到的所有表，而且，如果 sql 语句使用了 表的别名，也必须对对应的别名加锁
- 可以通过多表显式加锁模拟事务操作。例如：
Lock tables thread_0 read ; Lock tables thread_1 read ;
Select max(post_id) from thread_0;
Select max(post_id) from thread_1;
//比较两个表的最大 post_id 并计算
Unlock tables;
在这个例子中，由于加了锁，可以避免其它进程在执行过程中对 post_id 的值进行破坏。 需要注意，使用 locktable 给表显式加锁时，必须同时取得所有涉及表的锁。MyISAM 不
支持锁升级。
- 可用 insert delayed 避免锁阻塞。在这种情况下，insert 语句会立刻返回，但此时数 据可能还没有插入，Myisam 会等其他线程释放锁的时候将该数据插入。注意这种用法有 一些限制，具体参加官方手册。
- 使用 show process list 查看进程有锁等待的情况。
- 使用 show innodb status 查看 innodb 当前锁状态
- show status like '%_lock%';
- 使用 mysqladmin debug 查看哪个进程加了锁
- show mutex status 查看 innodb 锁的精确位置（精确到代码行）。
- Innodb:
- myisam：
4 锁 的 监 控 与 调 试
5 锁 相 关 配 置
innodb_locks_unsafe_for_binlog
innodb_autoinc_lock_mode（mysql5.1.22 后的版本引入）
647
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
