【MYSQL排错指南学习笔记（二）并发问题-锁】

mysql服务器用锁和事务来处理对其表的并发访问。
当线程请求数据集的时候就会加锁，可以是表、行、页或者元数据。当线程结束处理特定的数据集中之后，就会释放锁。
数据库事务时处理一致性和可靠性工作的最小单位，使用户可以避免与其他事物交互时可能产生的风险。事务的隔离等级控制其他并发操作中的变化对本事务是否可见。

读锁 S（共享锁允许并发线程读取加锁的操作，但禁写数据
写锁 X（排它锁）阻止其他线程的读写操作

select语句或者lock table t1 read给语句显式加锁，数据库加读锁
修改或者lock table t1 write 给语句显式加锁，数据库加写锁

mysql有4种类型的锁：表锁，行锁，页锁（比较少见的BDB存储引擎中存在），元数据锁。
元数据锁是mysql5.5版本的新特性，该锁仅对表中的的元数据启用。当有线程开始使用表的时候，元数据锁会锁住表的所有原始数据。元数据是DDL语句的更改信息，如create、drop、alter等语句。

当访问表并且所使用存储引擎支持表锁时候，就会产生表锁，比如MyISAM引擎。
也可以在任何引擎上显式调用lock tables来产生表锁，5.5之前版本也可使用DDL操作产生表锁

在执行缓慢的时候，怀疑是并发进程影响了查询，
执行show processlist\G
show full processlist\G 是完整版

InnoDB是当前使用行锁的主要存储引擎
新查询等待innodb_lock_wait_timeout参数里设置时间（默认50秒）

如果无法使用通过唯一键（unique）解析的where条件访问表，我们就无法并行修改任何行，因为存储引擎无法判断其余线程是否要更新相同的行。

查询INFORMATION_SCHEMA.PROCESSLIST表，与show processlist命令展示信息相同，但是并发进程较多时候，可以排序。
select * from INFORMATION_SCHEMA.PROCESSLIST\G

为了确定在InnoDB中一个请求是否阻塞，使用 InnoDB监控器

SHOW ENGINE INNODB STATUS \G

该命令是InnoDB监控机制的一部分。在分析并发语句事务的作用的时候尤为有用。

上述信息表明该查询在等待锁

事务

mysql在存储引擎级别提供事务支持
mysql中使用START TRANSACTION 或BEGIN 语句启动事务，通过COMMIT提交事务，通过ROOLBACK回滚事务（取消事务）
启动多语句事务的方法是将autocommit变量的值设置为0，这将覆盖mysql的默认行为，即在每条语句后发送一个隐式提交命令。当autocommit设置为0后，显式调用commit或者roolback命令，则在这之后，下一条语句会自动新事务。autocommit默认为1，每条语句都会立刻提交。

隐藏查询
事务由很多语句组成的时候，看不到查询，事务也可能锁住了行
InnoDB插件丰富了监控功能，在INFORMATION_SCHEMA库中保存一些相关表
INNODB_LOCKS INNODB_LOCK_WAITS 保存已经获取的锁和等待的锁的信息
INNODB_TRX 保存正在执行的事务信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G

INNODB_LOCK_WAITS表里:
requesting_trx_id 列就是‘挂起’的事务id
blocking_trx_id 列为持有锁的事务的id
requested_lock_id 列为被请求的锁的id信息
blocking_lock_id 列为被阻塞的锁的id信息

在INNODB_TRX表里获得正在阻塞中的事务的mysql进程的id
trx_id 正在阻塞的事务id
trx_mysql_thread_id 可以与show processlist命令的id列校对，应该一致

trx_state 等待的事务状态为lock wait，运行的事务状态为running

死锁查看 SHOW ENGINE INNODB STATUS

元数据锁的获取不依赖使用的存储引擎。无论设置autocommit=0的MyISAM引擎还是BEGIN或者START TRANSCATION语句显式声明的事务，连接都会取得元数据锁。show processlist\G 看到这些信息，看到一些
waiting for table metadata lock的语句。
查看引擎 show create table t1\G

InnoDB事务引起的锁问题InnoDB监控器可以减轻工作
打开监控器，就会定期将消息转储到错误日志文件中，与SHOW ENGINE INNODB STATUS输出类似
要打开InnoDB监控器，需要在任何数据库中创建一个innodb_monitor表
mysql test -A
create table innodb_monitor(f1 INT) ENGINE=InnoDB;

当登录数据库使用-A参数时，就不预读数据库信息。在尝试调试与并发有关的问题很有用，防止被阻塞

给需要大缓冲区的会话（连接）设置，会话级别动态设置
set session join_buffer_size=102410241024;

其他锁问题
可以通过performance_schema来获取额外的信息

首先检查表mutex_instance 存放自服务器启动以来所有的冲突（有一部分没有使用，在select中忽略，仅获取locked_by_thread_id不为空的记录）
SELECT * FROM performance_schema.MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID is not null\G

为了找出谁在等待这些冲突是，可以去查询EVENTS_WAITS_CURRENT表
SELECT THREAD_ID,EVENT_ID,EVENT_NAME,SOURCE,TIMER_START,OBJECT_INSTANCE_BEGIN,OPERATION FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL)\G

输出中的THREAD_ID,是内部mysqld分配的线程的实际编号，而不是帮助找到死锁原因的连接线程的编号。
要查找，可以查询THREADS表
select * from THREADS\G

举例子:

SHOW ENGINE INNODB STATUS

该进程没有任何等待操作，却锁住了两个表
MYSQL>KILL 10
show processlist\G

SELECT * FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID is not null\G

empty

SELECT THREAD_ID,EVENT_ID,EVENT_NAME,SOURCE,TIMER_START,OBJECT_INSTANCE_BEGIN,OPERATION FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID IN(SELECT LOCKED_BY_THREAD_ID FROM MUTEX_INSTANCES WHERE LOCKED_BY_THREAD_ID IS NOT NULL)\G

empty