Mysql 隔离级别和锁

Mysql 隔离级别和锁

mysql 数据库锁

• 四种隔离级别
• 可能导致的问题
• 锁的细节
• *如何分析问题
• 注意事项和优化方法

---

四种隔离级别

• 加粗 事务的4大特征ACID Atomicity Consistency Isolation Durability
  脏读 不可重复读 幻读 加锁读

隔离级别	脏读	不可重复读	幻读	加锁读
READ UNCOMMITTED	Yes	YES	YES	NO
READ COMMITTED	NO	YES	YES	NO
REPEATABLE READ	NO	NO	YES	NO
SERIALIZABLE	NO	NO	NO	YES

脏读（Drity Read）

某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读（Non-repeatable Read）

在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读（Phantom Read）

在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

MVCC

隔离性通过行记录和read_view两个基本数据结构实现。行记录：每行有两个隐藏列，DATA_TRX_ID和DATA_ROLL_PTR。read_view通过两个变量控制，low_limit_id和up_limit_id。

不同隔离级别实现机制不一样，好处是不加锁，对CRUD分别说明:　
Snapshot read & Current read

项目	价格
Snapshot read	Select …
Current read	(1)Select … lock in share mode (2)Select … for update (3)Insert, update, delete

锁

MyISAM

只有读读不阻塞。
自动加表锁，select自动加读锁，其他自动加写锁。
手动加表锁，”lock talbe t1 read local, t2 write”、unlock tables;不支持锁升级，读锁只读，不可操作未锁定的表。
并发插入 concurrent_insert
锁的争用 low-priority-updates、max_write_lock_count

InnoDB

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。
只有通过索引条件检索数据，InnoDB才使用行级锁，否则使用表锁。
不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
使用相同的索引键会冲突。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。
检索值的数据类型与索引字段不同，虽然能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。
理解间隙锁：Record lock, Gap lock, Next-key lock
恢复和复制机制对锁的影响，insert … select …，控制变量innodb_locks_unsafe_for_binlog

核心并发数 innodb_thread_concurrency，innodb_concurrency_tickets

InnoDB - 表锁

事务需要更新大部分或全部数据，表又比较大。
事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。

使用LOCK TABLES虽然可以给InnoDB加表级锁，但表锁不是由InnoDB存储引擎层管理的，而是由MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁

InnoDB -死锁

MyISAM无死锁。
死锁自动检测，变量innodb_lock_wait_timeout。
尽量约定以相同的顺序来访问表。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录。
如果要更新记录，应该直接申请足够级别的锁。
RR级别同时加排它锁，但是记录不存在，都可以加锁成功，但再插入会导致死锁，改为RC可解决。
RC级别同时加排它锁，若没有就插入，第一个成功后第二个会因主键重复失败，但仍会获得排它锁，第三个申请排它锁会造成死锁。可改为直接做插入操作，然后再捕获主键重异常，错误立即ROLLBACK。

InnoDB - 分析sql的语句

列出正在打开的表： show open tables where in_use > 0

列出当前线程：show full processlist

查看lock相关变量：show status like ‘%lock%’

查看引擎信息：show engine innodb status\G

查看超时信息： show variables like ‘%timeout%‘

查看并发信息：show variables like ‘%concurren%‘

注意事项和优化方法

各种变量首先确定和优化。

尽量使用较低的隔离级别。

精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。

选择合理的事务大小，小事务发生锁冲突的几率也更小。

给记录集显示加锁时，最好一次性请求足够级别的锁。

访问一组表时，应尽量以相同的顺序访问各表，对同一个表，尽可能以固定的顺序存取行。

尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。

不要申请超过实际需要的锁级别，除非必须，查询时不要显示加锁。