MySql事务和锁学习笔记

MyISAM表锁

MyISAM不支持事务，所以MyISAM的锁都是锁整张表的。

表共享读锁

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

-- 获取表的读锁
lock table 表名 read;

-- 如果另外一个会话已经拿到表的写锁，申请读锁会被阻塞

-- 如果另外一个会话拿到的是读锁，不会当前获取读锁有影响

-- 成功拿到读锁之后，当前会话可以正常的查询

-- 当有一个会话拿到表的读锁之后， 另外一个会话执行写操作会被阻塞

-- 拿到读锁之后， 无法执行写操作
-- ERROR 1099 (HY000): Table 'tab1' was locked with a READ lock and can't be updated"

-- 不能查询没有锁定的表
-- ERROR 1100 (HY000): Table 'tab2' was not locked with LOCK TABLES

-- 释放读锁
unlock tables;

表独占写锁

对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作和读操作之间是串行的！

-- 获取表的写锁
lock table 表名 write;

-- 没有其他会话获取这张表的读锁和写锁，才可以拿到对这张表的读锁

-- 拿到读锁之后可以正常执行读和写操作

-- 其他没有拿到锁的会话执行查询和插入都会被阻塞

-- 释放写锁
unlock tables;

tip:

• 在非自动提交的模式下， 执行写操作之后没有commit， 就算释放了锁， 其他会话也无法申请到读或写锁。
• MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁。

如何分析表锁的竞争状态

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺

show status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 146   | 指的是能够立即获得表级锁的次数
| Table_locks_waited    | 1     | 指的是不能立即获取表级锁而需要等待的次数
+-----------------------+-------+

所以如果Table_locks_waited值比较大的话，说明锁竞争比较激烈， 考虑使用innodb存储引擎。

InnoDB锁

InnoDB支持事务， 支持针对行进行枷锁。

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

• 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

事务

查看&设置事务模式

-- 查看当前绘画是否是自动提交 1:开启 0:关闭
select @@autocommit

-- 查看当前系统的是否自动提交
select @@global.autocommit;

-- 设置关闭
set autocommit = 0;

-- 设置全局的自动提交开关
set global autocommit = 0;

-- 查看当前会话的事务隔离级别
/*
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读 (默认)
serializable 序列化执行，串行执行 (锁表)
*/
select @@tx_isolation

-- 查看当前系统的事务隔离级别
select @@global.tx_isolation;

-- 设置当前会话的事务的隔离级别
set session transaction isolation level 隔离级别;

-- 设置当前系统的事务隔离界别
set global transaction isolation level 隔离级别;

手动开始和提交事务

-- 开始一个事务
start transaction;

-- 提交一个事务
commit;

脏读， 不可重复读， 幻读

脏读: A 事务还未提交， B事务中就能读取到A的更新结果
不可重复读: A 更新了数据，未提交的时候B事务中看不到更新结果， 但是A提交了事务之后，B在没有提交事务的情况下读到了A的更新结果。
幻读：A 事务中添加了一条记录，并提交了事务， B 事务中虽然读取不到这条记录，但是插入同一条记录的时候会提示重复记录。

分析InnoDB行锁的竞争状态

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

show status like "innodb_row_lock%";
/*
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |  当前正在等待锁的数量
| Innodb_row_lock_time          | 179924 |  从系统启动到现在锁定总时间长度
| Innodb_row_lock_time_avg      | 14993  |  每次等待所花平均时间
| Innodb_row_lock_time_max      | 51175  |  从系统启动到现在等待最长的一次所花的时间长度
| Innodb_row_lock_waits         | 12     |  系统启动到现在总共等待的次数
+-------------------------------+--------+
*/

InnoDB 行锁模式及加锁方法

mysql InnoDB引擎默认的修改数据语句update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型

• 共享锁： 又称读锁，对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。

select * from table lock in share mode

• 排它锁： 又称写锁，对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。

select * from table for update

InnoDB加锁的实现方式

InnoDB行锁是通过给 索引 上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

总结

对于MyISAM的表锁，主要讨论了以下几点：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，本文主要讨论了以下几项内容：
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。