innodb锁(三)
来源:互联网 发布:linux平台安装mongodb 编辑:程序博客网 时间:2024/06/16 04:13
Innode引擎监控的开启的方法
锁监控:
打开innodb的锁监控:
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
5.6.16可以使用: --两个都需要打开
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
表空间监控:
打开innodb表空间监控:
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
表监控:
打开innodb表监控:
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
打开监视器以后
innodb_monitor和innodb_lock_monitor会每隔15秒会向错误日志中记录InnoDB监控信息;
innodb_table_monitor和innodb_tablespace_monitor是每隔64秒;
innodb_monitor和innodb_lock_monitor两种监视器的输出结果基本类似,后者会有更多关于锁的信息,而前一个实际上就是show innodb status;
innodb_table_monitor会将系统中所有innodb的表的一些结构和内部信息输出;
innodb_tablespace_monitor输出的是tablespace的信息,注意该monitor输出的只是共享表空间的信息,如果使用innodb_file_per_table为每个表使用独立的表空间,则这些表空间的信息是不会包含在输出中的。
停止InnoDB监控
drop table innodb_monitor;
drop table innodb_lock_monitor;
drop table innodb_table_monitor;
drop table innodb_tablespace_monitor;
几个重点的锁类型需要关注下
如果辅助索引上的搜索及锁定是排它的,则会取回其相应的聚集索引,并且在它上面加锁;
对无索引的字段检索更新时升级成表级别锁(表中全部记录被锁,除非在RC或innodb_locks_unsafe_for_binlog=1 模式下 采用semi-consitent read机制);
Insert into T select … from S where T表上排它record lock 事务隔离级别为RC或者启用innodb_locks_unsafe_for_binlog并且隔离级别不是serializable时,S表上采用无锁一致性读,否则(rr),加排它next-key lock(RC不加锁。RR加next-key lock);
Insert 排它record lock,而非next-key lock,但在写入新记录之前需要加意向gap lock(insertion intention gap lock);
Insert…on duplicate key update 排它next-key lock(即使被update的记录上)会同时并发执行;
Create table…select 和insert…select 一样;
Replace 没冲突/重复时 和insert一样 否则(有冲突时先delete后insert)加next-key-lock;
Replace into t select … from S where 或者update T … where col IN(SELECT…FROM S..),都会在S表上加next-key lock;
Auto..INCREMENT列上写新数据时,索引末尾设置排它锁,请求自增列计数器时,INNODB使用一个AUTO-INC表锁,只对请求的那个SQL有影响,不会影响整个事务,该锁被持有时,其他会话不能往INNODB表中写入新行;
Select…from 一致性非锁定读除非是serializable隔离级别,在其影响的索引记录上设置一个共享锁(简单的select…from是不加锁的);
Lock in shared mode,使用共享next-key lock;
For update使用排它next-key lock锁,会阻止lock in shared mode请求;
Update/delete,排它next-key lock.
死锁
死锁不会卡,有一个会立刻回滚,再次提交即可,Show engine innodb status 只显示最后死锁的信息,设置innodb_print_all_deadlocks=1,在日志中记录全部死锁信息;
自动检测死锁,并优先回滚最小事务(影响较小的事务),加表锁时,不会发生死锁;
事务中如果select调用存储函数/存储过程失败了,对用的SQL会回滚事务,如果再显示执行ROLLBACK,那么整个事务都回滚;
事务回滚时,会释放全部的锁,个别情况下,如果个别SQL因为某些错误回滚事务的话它所持有的行锁可能无法释放,因为INNODB的行锁信息并没有记录时那个SQL持有的,这时候建议执行一次显示的ROLL BACK。
避免死锁
事务尽快提交,小事务越不容易发生死锁;
加for update lock in shared mode读锁时最好降低事务隔离级别,例如用RC级别降低死锁发生概率;
事务中涉及多个表,或者涉及多行记录时,每个事务的操作顺序都要保持一致,降低死锁发生概率,最好用存储过程/存储函数固化;
通过索引等方式优化SQL效率,降低死锁发生概率(减少扫描/锁范围,降低概率。
锁监控:
打开innodb的锁监控:
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
5.6.16可以使用: --两个都需要打开
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
表空间监控:
打开innodb表空间监控:
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
表监控:
打开innodb表监控:
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
打开监视器以后
innodb_monitor和innodb_lock_monitor会每隔15秒会向错误日志中记录InnoDB监控信息;
innodb_table_monitor和innodb_tablespace_monitor是每隔64秒;
innodb_monitor和innodb_lock_monitor两种监视器的输出结果基本类似,后者会有更多关于锁的信息,而前一个实际上就是show innodb status;
innodb_table_monitor会将系统中所有innodb的表的一些结构和内部信息输出;
innodb_tablespace_monitor输出的是tablespace的信息,注意该monitor输出的只是共享表空间的信息,如果使用innodb_file_per_table为每个表使用独立的表空间,则这些表空间的信息是不会包含在输出中的。
停止InnoDB监控
drop table innodb_monitor;
drop table innodb_lock_monitor;
drop table innodb_table_monitor;
drop table innodb_tablespace_monitor;
几个重点的锁类型需要关注下
如果辅助索引上的搜索及锁定是排它的,则会取回其相应的聚集索引,并且在它上面加锁;
对无索引的字段检索更新时升级成表级别锁(表中全部记录被锁,除非在RC或innodb_locks_unsafe_for_binlog=1 模式下 采用semi-consitent read机制);
Insert into T select … from S where T表上排它record lock 事务隔离级别为RC或者启用innodb_locks_unsafe_for_binlog并且隔离级别不是serializable时,S表上采用无锁一致性读,否则(rr),加排它next-key lock(RC不加锁。RR加next-key lock);
Insert 排它record lock,而非next-key lock,但在写入新记录之前需要加意向gap lock(insertion intention gap lock);
Insert…on duplicate key update 排它next-key lock(即使被update的记录上)会同时并发执行;
Create table…select 和insert…select 一样;
Replace 没冲突/重复时 和insert一样 否则(有冲突时先delete后insert)加next-key-lock;
Replace into t select … from S where 或者update T … where col IN(SELECT…FROM S..),都会在S表上加next-key lock;
Auto..INCREMENT列上写新数据时,索引末尾设置排它锁,请求自增列计数器时,INNODB使用一个AUTO-INC表锁,只对请求的那个SQL有影响,不会影响整个事务,该锁被持有时,其他会话不能往INNODB表中写入新行;
Select…from 一致性非锁定读除非是serializable隔离级别,在其影响的索引记录上设置一个共享锁(简单的select…from是不加锁的);
Lock in shared mode,使用共享next-key lock;
For update使用排它next-key lock锁,会阻止lock in shared mode请求;
Update/delete,排它next-key lock.
死锁
死锁不会卡,有一个会立刻回滚,再次提交即可,Show engine innodb status 只显示最后死锁的信息,设置innodb_print_all_deadlocks=1,在日志中记录全部死锁信息;
自动检测死锁,并优先回滚最小事务(影响较小的事务),加表锁时,不会发生死锁;
事务中如果select调用存储函数/存储过程失败了,对用的SQL会回滚事务,如果再显示执行ROLLBACK,那么整个事务都回滚;
事务回滚时,会释放全部的锁,个别情况下,如果个别SQL因为某些错误回滚事务的话它所持有的行锁可能无法释放,因为INNODB的行锁信息并没有记录时那个SQL持有的,这时候建议执行一次显示的ROLL BACK。
避免死锁
事务尽快提交,小事务越不容易发生死锁;
加for update lock in shared mode读锁时最好降低事务隔离级别,例如用RC级别降低死锁发生概率;
事务中涉及多个表,或者涉及多行记录时,每个事务的操作顺序都要保持一致,降低死锁发生概率,最好用存储过程/存储函数固化;
通过索引等方式优化SQL效率,降低死锁发生概率(减少扫描/锁范围,降低概率。
0 0
- innodb锁(三)
- MySQL InnoDB锁机制(三)
- MySQL——InnoDB锁问题(三)
- mysql的innodb存储引擎(三)
- mysql 默认引擎innodb 初探(三)
- InnoDB源码分析--缓冲池(三)
- InnoDB源码分析--缓冲池(三)
- MySQL优化三(InnoDB优化)
- MySQL优化三(InnoDB优化)
- MySQL优化三(InnoDB优化)
- MySQL优化三(InnoDB优化)
- MySQL优化三(InnoDB优化)
- MySQL优化三(InnoDB优化)
- MySQL优化三(InnoDB优化)
- innodb锁(一)
- Innodb 锁
- InnoDB 锁
- InnoDB 锁
- 给Eclipse提速的7个技巧
- Apache Commons工具集简介
- android应用程序启动解决黑屏及全屏显示时因数据加载缓慢出现标题栏问题解决
- Eclipse项目分包管理
- 【bzoj4350】括号序列再战猪猪侠 区间DP
- innodb锁(三)
- Android逆向---Fiddler简易使用教程之抓取https包
- 去除UITableView底部多余行及分割线
- PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes
- Android之Glide获取图片Path、Bitmap用法
- Mysql字符集设置
- 一步一步开发安卓应用 手机端网上购物系统
- 【转】方便好使的java.util.Properties类
- cocoapods的安装,使用,卸载,以及你可能会遇到的坑