innodb 锁机制

innodb存储引擎不需要锁升级，因为一个锁和多个锁的开销是相同的。

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。Innodb引擎中使用锁的地方有多个：在行级别上对表数据上锁；操作缓冲池中的LRU列表，删除，添加，移动LRU列表中的元素。

锁信息的查看命令：

  • show engine innodb status;
  • information_schema架构下的表：innodb_trx，innodb_locks，innodb_lock_waits

锁的类型

innodb实现了两种标准的行级锁：

  • 共享锁（S LOCK),允许事务读一行数据
  • 排他锁（X lock），允许事务删除或更新一行数据

锁兼容：如果一个事务T1已经获得了r行的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据。

锁不兼容：如果T3想获得r行的x锁，则必须等T1,T2释放行r上的S锁

 

 

S锁和X锁的兼容性
  X S
X 不兼容 不兼容
S 不兼容 兼容

innodb支持两个钟意向锁（在innodb中即为表锁）：

  • 意向共享锁（IS lock），事务想要获得一张表中某几行的共享锁
  • 意向排他锁（IX lock），事务想要获得一张表中某几行的排他锁

查看innodb隔离级别：

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

　　

实验过程：

建表test：

mysql> CREATE TABLE `test` (
    ->   `id` bigint(20) NOT NULL,
    ->   `name` varchar(20) NOT NULL DEFAULT "name",
    ->   PRIMARY KEY (id),
    ->   KEY `index_name` (`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.99 sec)

填充数据：

mysql> insert into test (id) values(1),(2),(3),(9);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | name |
|  2 | name |
|  3 | name |
|  9 | name |
+----+------+
4 rows in set (0.00 sec)

　

实验数据：

说明：事务一，事务二都有数据，顺序是先执行事务一，再执行事务二。

 
事务一 事务二 innodb_trx innodb_locks innodb_lock_waits 
mysql> begin; select * from test where id=1 for update;
Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | name |
+----+------+
1 row in set (0.00 sec)

　　

   
mysql>  select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1074396261
                 trx_state: RUNNING
               trx_started: 2017-04-26 15:18:25
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 8950735
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         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: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

　　

 
mysql> select * from information_schema.INNODB_LOCKS\G
Empty set (0.00 sec)

　　

 
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
Empty set (0.00 sec)

　　
  
mysql> begin;select * from test where id=1 lock in share mode;
Query OK, 0 rows affected (0.00 sec)

　　

 
mysql>  select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1074397998
                 trx_state: LOCK WAIT
               trx_started: 2017-04-26 15:21:02
     trx_requested_lock_id: 1074397998:5594:3:2
          trx_wait_started: 2017-04-26 15:21:02
                trx_weight: 2
       trx_mysql_thread_id: 8959888
                 trx_query: select * from test where id=1 lock in share mode
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         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: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 1074396261
                 trx_state: RUNNING
               trx_started: 2017-04-26 15:18:25
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 8950735
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         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: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

　　

 
mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 1074397998:5594:3:2
lock_trx_id: 1074397998
  lock_mode: S
  lock_type: RECORD
 lock_table: `ztest`.`test`
 lock_index: PRIMARY
 lock_space: 5594
  lock_page: 3
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 1074396261:5594:3:2
lock_trx_id: 1074396261
  lock_mode: X
  lock_type: RECORD
 lock_table: `ztest`.`test`
 lock_index: PRIMARY
 lock_space: 5594
  lock_page: 3
   lock_rec: 2
  lock_data: 1
2 rows in set (0.00 sec)

　　

 
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 1074397998
requested_lock_id: 1074397998:5594:3:2
  blocking_trx_id: 1074396261
 blocking_lock_id: 1074396261:5594:3:2
1 row in set (0.00 sec)

　　

 
mysql> update test set name="name1" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | name  |
|  3 | name  |
|  9 | name  |
|  1 | name1 |
+----+-------+
4 rows in set (0.00 sec)

　　

 
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | name |
|  2 | name |
|  3 | name |
|  9 | name |
+----+------+
4 rows in set (0.00 sec)

　　该查询下，id为1的行，并没有修改name，因为事务一的修改并没有提交。

       
mysql> commit
-> ;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 2 | name |
| 3 | name |
| 9 | name |
| 1 | name1 |
+----+-------+
4 rows in set (0.00 sec)

　　

   
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | name |
|  2 | name |
|  3 | name |
|  9 | name |
+----+------+
4 rows in set (0.00 sec)

　　事务一，已经提交，该查询下，id为1的行，name没有发生变化，表明在RR隔离级别下的，可重复读：同一个事务的，同一个select读出的数据完全一致。

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

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | name  |
|  3 | name  |
|  9 | name  |
|  1 | name1 |
+----+-------+
4 rows in set (0.00 sec)

　　两个事务都结束了。

      
 实验二    
 事务一  事务二 innodb_trx  innodb_locks  innodb_lock_waits
  
mysql> begin;select * from test where id>0 and id<5 for update;
Query OK, 0 rows affected (0.00 sec)

+----+-------+
| id | name  |
+----+-------+
|  2 | name  |
|  3 | name  |
|  1 | name1 |
+----+-------+
3 rows in set (0.00 sec)

　　

 
mysql> begin;update test set name="nihao" where id=1;
Query OK, 0 rows affected (0.00 sec)

select * from test;

　　事务二的两个操作都被阻塞

 		     
mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 1074413840:5594:3:6
lock_trx_id: 1074413840
  lock_mode: X
  lock_type: RECORD
 lock_table: `ztest`.`test`
 lock_index: PRIMARY
 lock_space: 5594
  lock_page: 3
   lock_rec: 6
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 1074413574:5594:3:6
lock_trx_id: 1074413574
  lock_mode: X
  lock_type: RECORD
 lock_table: `ztest`.`test`
 lock_index: PRIMARY
 lock_space: 5594
  lock_page: 3
   lock_rec: 6
  lock_data: 1
2 rows in set (0.00 sec)

　　

 
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 1074413840
requested_lock_id: 1074413840:5594:3:6
  blocking_trx_id: 1074413574
 blocking_lock_id: 1074413574:5594:3:6
1 row in set (0.00 sec)

　　

 
  
mysql> update test set name="fjsld" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | fjsld |
|  2 | name  |
|  3 | name  |
|  9 | name  |
+----+-------+
4 rows in set (0.00 sec)

　　

        
  
mysql> commit;
Query OK, 0 rows affected (0.02 sec)

　　

 
mysql> update test set name="nihao" where id=1;


Query OK, 1 row affected (1 min 45.44 sec)
Rows matched: 1  Changed: 1  Warnings: 0

　　

事务一提交，事务二更新成功 

事务一优先获取了X锁，此后，事务二想要获取X锁，需要等候

      

 

常见概念：

  1. select ...for update
    1. 锁定查询出来的行，其他事务要修改该行，需要等待,该行会上X锁
  2. select ...lock in share mode;
    1. 锁定查询出来的行，其他事务需要修改改行，需要等待，该行会上S锁
  3. 四种事务隔离级别：
    1. RR:可重复读
    2. RC：提交读
    3. RU：非提交读
    4. SEARILIZED:事务串行执行
  4. innodb支持的两种级别举例分析
    1. RR:repeatable read：可重复读
    2. RC:read commit：提交读
  5. 脏读，幻读，不可重复读
    1. 脏读
      1. 事务一，能读到事务二还没有提交的更改信息
      2. 隔离级别最低的RU中会出现此种情况
    2. 幻读
      1. 不可重复读的特殊情况：同一条sql语句，是范围查找类型的，两次得到的结果不一样
      2. 事务二在某个范围内插入一条数据，并提交；事务一，再次范围查找得到不一样的结果
      3. innodb的RR下，此种类型不会出现
    3. 不可重复读
      1. 在同一个事务中，同样一条sql语句，执行两次，得到的结果不一样
      2. 适用场景：update
      3. innodb的RR下，此种类型不会出现
  6. 设置innodb_lock_wait_timeout
    1. 相关操作
      1. mysql> show global variables like "%timeout%";
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 120      |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
11 rows in set (0.00 sec)

mysql> set innodb_lock_wait_timeout=3600;
Query OK, 0 rows affected (0.00 sec)

        　　

posted @ 2017-04-26 15:36  玛吉  阅读(223)  评论(0)  编辑  收藏  举报