MySQL 锁问题

一、MySQL中不同的存储引擎支持不同的锁机制

(A) MyISAM 和 MEMORY 支持表级锁

(B) BDB 支持页面锁，也支持表级锁

(C) InnoDB 支持行级锁，也支持表级锁，默认是行级锁。

这三种锁的特性如下：

表级锁：开销小，加锁快，不会死锁，粒度大，冲突概率高，并发度低

行级锁：开销大，加锁慢，会死锁，粒度小，冲突概率低，并发度高

页面锁：开销中，加速中，会死锁，粒度中，冲突概率中，并发度中

二、MyISAM表锁

(A) 检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上表锁的争夺情况。

(B) 表锁有分为：共享读锁和独占写锁

(C) 在查询语句前，会自动给涉及的所有表加读锁，在更新操作前，会自动给涉及的所有表加写锁。

(D) 总是一次性获得所需的所有锁，这样就不会出现死锁。同时SQL中出现的表别名也需要锁定。

(E) 并发插入：一定条件下，MyISAM表支持查询和插入，系统变量concurrent_insert 是否支持并发插入

0：不允许并发插入

1：表中没有空洞，运行读表的同时，另一个进程表尾插入

2：运行表尾插入。

三、MyISAM的锁调度

(A) 写优先策略：可通过参数 low-priority-updates 修改；通过命令修改；修改属性等方法。

(B) 注意：一条长时间运行的查询语句可能会使写进程”饿死“。

四、InnoDB锁问题

一是支持事务、二是采用行级锁

事务是一组SQL语句组成的逻辑处理单元，具有ACID属性，原子性、一致性、隔离性和持久性。

并发事务访问带来的问题：更新丢失、脏读、不可重复读、幻读。

(A) 可以通过检测InnoDB_row_lock状态变量来分析系统上行锁的争夺情况

(B) InnoDB实现了两种类型的行锁：共享锁和排他锁。内部使用了两种表级别的意向锁。

(C) 行锁是通过给索引上的索引项加锁来实现的。意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

(D) 注意InnoDB的锁机制是间隙锁，这将导致锁定比实际更多的记录行，尽量采用相等条件而不是范围条件来避免此类问题。同时：如果使用相等条件给一个不存在的记录加锁，也会导致间隙锁。

五、回复和复制的需要，对InnoDB锁机制的影响

(A) MySQL通过BINLOG记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句。并以此实现MySQL数据库的恢复和主从复制。

MySQL的恢复是SQL语句级别的，也就是重新执行BINLOG中的SQL语句。

Binlog是按照事务提交的先后顺序记录的，恢复也是按照这个顺序。

根据上面的两个特点：我们知道一个事务提交前，其他事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

像 insert...select... 和 create table... select... 语句，虽然源表是查询，还是会对源码加锁，这是为了保证恢复和复制的一致性。不建议使用此种SQL语句。

(B) 不同隔离级别下的一致性读及锁的差异

不同隔离级别下，InnoDB实现一致性读和隔离级别的手段是不同的。

(C) 默认应该使用行锁，某些情况考虑使用表锁

事务需要更新大部分了或者全部数据，表又比较大。

事务设计多个表，比较复杂，可能引起死锁。

注意：表锁是由MySQL Server负责的，需要正确的设置。需要先将autocommit设为0，才给表加锁，事务结束前不要用unlock tables 释放表锁，其会隐式地提交事务；commit或rollback 并不能释放用lock tables 加的表级锁。

六、关于死锁

InnoDB一般都能自动检测到死锁，并释放某个事务并回退。但在设计外部锁和表级锁时，不能完全可能。需要设置锁等待超时参数 innodb_lock_wait_timeout 来解决。此参数可解决大量事务挂起的问题。

(A) 不同的程序尽量以相同的顺序来访问多个表。

(B) 批量方式处理数据时，事先对数据排序，每个线程固定顺序来处理记录，降低死锁出现的可能。

(C) 提前申请排他锁，而不是先申请共享锁，需要时再申请排他锁。

(D) 在程序设计中捕获和处理死锁是一个好的编程习惯。

(E) 使用 show innodb status 来查看事务的状态。

七、小结