MySQL中的锁(InnoDB存储引擎)

MySQL中的锁(lock)

lock对象：数据库中的对象，如表、页、行。
lock释放：事务commit或者rollback以后(不同事务隔离级别释放的时间可能不同)

锁的分类

• 行级锁
  1.共享锁
  2.排他锁
  共享锁是兼容锁，排他锁是不兼容锁。
  兼容指的是对同一记录锁的兼容性情况
• 2意向锁(表锁show)
  1.意向共享锁
  2.意向排他锁
  目的:支持在不同粒度上进行加锁操作，这种锁允许事务在行级上的锁和表级上的锁同时存在

查看锁的信息

在INFORMATIOIN_SCHEMA架构上添加了表INNODB_TRX INNODB_LOCKS INNODB_LOCK_WAITS，通过查看这三张表可以分析事务中可能存在的锁的情况

前期准备:
1.创建表

CREATE TABLE t_bitfly (
  id bigint(20) NOT NULL DEFAULT '0',
  value varchar(32) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据

 mysql> insert into t_bitfly (1, 'a'),(2, 'b'),(3,'c'),(4,'d'),(5,'e');

• 查看INNODB_TRX表

session1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_bitfly where id < 4 for update;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
+----+-------+
3 rows in set (0.00 sec)

session2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_bitfly where id >= 4 for update;

session1:

mysql> select * from information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 2754
                 trx_state: LOCK WAIT
               trx_started: 2018-04-20 13:44:49
     trx_requested_lock_id: 2754:116:3:5
          trx_wait_started: 2018-04-20 14:00:04
                trx_weight: 2
       trx_mysql_thread_id: 13
                 trx_query: select * from t_bitfly where id >= 4 for update
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 2753
                 trx_state: RUNNING
               trx_started: 2018-04-20 13:42:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 12
                 trx_query: select * from information_schema.INNODB_TRX
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 4
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

为什么会引起死锁，可以查看之前写的文章InnoDB存储引擎行级锁引起锁争用问题
字段说明：
trx_id:事务ID
trx_state:当前事务的状态
trx_started:事务开始的时间
trx_requested_lock_id:等待事物的锁ID，只有当trx_state为LOCK_WAIT时才会有值，否则为null
trx_weight:事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要进行回滚时，会选择该值最小的事务进行回滚
trx_wait_started:事务等待开始的时间
trx_mysql_thread_id:MySQL中的线程ID
trx_query:事务运行的SQL语句
通过查看INNODB_TRX表信息后知道了trx_id为2754的事务处于LOCK WAIT状态，而trx_id为2753的事务处于RUNNING状态

• 查看INNODB_LOCKS表

mysql>  select * from information_schema.INNODB_LOCKS\G;
*************************** 1. row ***************************
    lock_id: 2754:116:3:5
lock_trx_id: 2754
  lock_mode: X
  lock_type: RECORD
 lock_table: `study`.`t_bitfly`
 lock_index: PRIMARY
 lock_space: 116
  lock_page: 3
   lock_rec: 5
  lock_data: 4
*************************** 2. row ***************************
    lock_id: 2753:116:3:5
lock_trx_id: 2753
  lock_mode: X
  lock_type: RECORD
 lock_table: `study`.`t_bitfly`
 lock_index: PRIMARY
 lock_space: 116
  lock_page: 3
   lock_rec: 5
  lock_data: 4
2 rows in set, 1 warning (0.00 sec)

字段说明
lock_id: 锁的ID
lock_trx_id: 事务ID
lock_mode: 锁的模式
lock_type: 锁的类型，表锁还是行锁
lock_table: 要加锁的表
lock_index: 锁住的索引
lock_space: 锁对象的space id
lock_page: 事务锁定页的数量，若是表锁，则该值为NULL
lock_rec: 事务锁定行的数量，若是表锁，则该值为NULL
lock_data: 事务锁定记录的主键值，若是表锁，则该值为NULL。该值并不是“可信的”值
通过查看INNODB_LOCKS表信息后知道了锁等待是因为两个事务都往tx_bitfly表上加X锁

• 查看INNODB_LOCK_WAITS表

mysql>  select * from information_schema.INNODB_LOCK_WAITS\G;
*************************** 1. row ***************************
requesting_trx_id: 2754
requested_lock_id: 2754:116:3:5
  blocking_trx_id: 2753
 blocking_lock_id: 2753:116:3:5
1 row in set, 1 warning (0.00 sec)

字段说明
requesting_trx_id: 申请锁资源的事务ID
requested_lock_id: 申请的锁的ID
blocking_trx_id: 阻塞的事务ID
blocking_lock_id: 阻塞的锁的ID
通过查看INNODB_LOCKS表信息后知道了事务2754申请2753事务上面的锁
因此我们可以通过分析三张表的信息来分析锁的执行情况

MVCC(多版本并发控制)

上面介绍的锁都是悲观锁，类似于java并发中的synchronize、Lock，MVCC则像乐观锁，类似于java并发中的cas，并不会对数据进行加锁，可以提高数据库的并发性

auto_increment实现使用的锁

参考
书籍：