Mysql 死锁过程及案例详解之插入意向锁与自增锁备份锁日志锁Insert Intention Lock Auto |
您所在的位置:网站首页 › MySQL自增 › Mysql 死锁过程及案例详解之插入意向锁与自增锁备份锁日志锁Insert Intention Lock Auto |
Mysql 插入意向锁与自增锁备份锁日志锁 插入意向锁Insert Intention Lock插入意向锁Insert intention locks是记录级别的,它通过“INSERT”关键词来向其它的事务传达插入的意向。插入意向锁针对的是将要插入的记录而不是已经存在的记录。下面我们通过例子来重现这个插入意向锁。 示意案例--Step1 在会话1里对city表ID大于4079的记录进行更新操作。 USE world; START TRANSACTION; SELECT * FROM city WHERE ID > 4079 FOR UPDATE; --Step2 在会话2里 新起个事务并插入一条记录。 SELECT PS_CURRENT_THREAD_ID(); /* PS_CURRENT_THREAD_ID() 50 */ START TRANSACTION; INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS','Northern Territory', 146000); --Step3 在会话3里查看插入意向锁的信息。这里的THREAD_ID来自Step2。 mysql> SELECT * FROM performance_schema.data_locks WHERE THREAD_ID = 50 \G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140113210997976:1067:140113107561216 ENGINE_TRANSACTION_ID: 38669 THREAD_ID: 50 EVENT_ID: 75 OBJECT_SCHEMA: world OBJECT_NAME: city PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140113107561216 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140113210997976:6:35:1:140113107558304 ENGINE_TRANSACTION_ID: 38669 THREAD_ID: 50 EVENT_ID: 75 OBJECT_SCHEMA: world OBJECT_NAME: city PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140113107558304 LOCK_TYPE: RECORD LOCK_MODE: X,INSERT_INTENTION LOCK_STATUS: WAITING LOCK_DATA: supremum pseudo-record 2 rows in set (0.00 sec) 这时不难发现回话2即THREAD_ID为50的事务当前的锁模式为X,INSERT_INTENTION即排他的插入意向锁,这里看到锁定的数据是“supremum pseudo-record”即该表的上界伪记录。这里“X”对应next key锁,但这里比较特殊,因为锁的是上界伪记录,很显然是锁定不了的(上界无法定义),这里其实是个gap lock即锁定当前表的最后记录和上界伪记录之间的记录。 自增锁Auto-increment Lock 自增锁Auto-increment Locks主要应用在一个表有自增列时,确保两个事务对表操作时有不重复的唯一的值是很有必要的。如果使用基于语句的日志记录到二进制日志时,还有进一步的限制,因为在重放语句时,除了第一行以外的所有行都将重新创建自动递增值。Innodb通过参数innodb_autoinc_lock_mode设置自增锁。 0:traditonal (每次都会产生表锁,该自增锁是表锁级别,且必须等待当前SQL执行完成后或者回滚掉才会释放。) 1:consecutive (会产生一个轻量锁,无论是单一或者批量的insert SQL,可以立即获得该锁,并立即释放,能保证复制,但自增值不一定严格连续。) 2:interleaved (不会锁表,来一个处理一个,并发最高,所有insert种类的SQL都可以立马获得锁并释放,无法保证复制。) 备份锁Backup Lock备份锁Backup Locks是mysql8.0里新引进的实例级别的锁,它主要是保证备份时数据库是一致的,它在备份时主要锁定以下语句: CREATE TABLE、CREATE TABLESPACE、RENAME TABLE、DROP TABLE、 CREATE USER、 ALTER USER、DROP USER、GRANT等。 备份锁可以通过如下语句产生和收回,前提是用户拥有BACKUP_ADMIN权限: LOCK INSTANCE FOR BACKUP UNLOCK INSTANCE 示意案例-- Step1 会话1里锁定数据库实例 LOCK INSTANCE FOR BACKUP; -- Step2 会话2里优化world数据库里的city表 use world OPTIMIZE TABLE city; -- Step3 会话3里查看锁的相关信息(以mysql命令行方式执行) SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_TYPE = 'BACKUP LOCK'\G *************************** 1. row *************************** OBJECT_TYPE: BACKUP LOCK OBJECT_SCHEMA: NULL OBJECT_NAME: NULL COLUMN_NAME: NULL OBJECT_INSTANCE_BEGIN: 4022252031312 LOCK_TYPE: SHARED LOCK_DURATION: EXPLICIT LOCK_STATUS: GRANTED SOURCE: sql_backup_lock.cc:101 OWNER_THREAD_ID: 29 OWNER_EVENT_ID: 8 *************************** 2. row *************************** OBJECT_TYPE: BACKUP LOCK OBJECT_SCHEMA: NULL OBJECT_NAME: NULL COLUMN_NAME: NULL OBJECT_INSTANCE_BEGIN: 2520403183328 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_base.cc:5400 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 19 2 rows in set (0.0007 sec) -- Step4 解锁数据库实例 UNLOCK INSTANCE; 在如上的例子里,会话1里进程id 29拥有一个备份锁(back lock),而会话2里有个进程是30的在等待备份锁释放。这里LOCK INSTANCE FOR BACKUP是个共享锁,而DDL语句是排他的意向锁。 日志锁Log Lock在创建备份时,通常需要包含与备份一致的日志位置信息,在Mysql 5.7时需要加一个全局读锁才能获取到这个信息,而在Mysql 8.0里InnoDB可以通过日志锁来读执行的全局事务标识(global transaction identifiers)又简称为GTIDs,二进制日志的位置、日志序列号(log sequence number 又简称为LSN)),而不再是读全局读锁。 日志锁Log Lock,可以阻止修改日志相关信息的操作。 示意案例-- 查看日志状态。 SELECT * FROM performance_schema.log_status\G *************************** 1. row *************************** SERVER_UUID: 59e3f95b-e0d6-11e8-94e8-ace2d35785be LOCAL: {"gtid_executed": "59e3f95b-e0d6-11e8-94e8- ace2d35785be:1-5343", "binary_log_file": "mysqlbin. 000033", "binary_log_position": 3874615} REPLICATION: {"channels": []} STORAGE_ENGINES: {"InnoDB": {"LSN": 7888992157, "LSN_checkpoint": 7888992157}} |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |