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%‘
注意事项和优化方法
各种变量首先确定和优化。
尽量使用较低的隔离级别。
精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
选择合理的事务大小,小事务发生锁冲突的几率也更小。
给记录集显示加锁时,最好一次性请求足够级别的锁。
访问一组表时,应尽量以相同的顺序访问各表,对同一个表,尽可能以固定的顺序存取行。
尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
不要申请超过实际需要的锁级别,除非必须,查询时不要显示加锁。