• 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_TRXG
*************************** 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_LOCKSG
Empty set (0.00 sec)

    　　

    		 
    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITSG
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_TRXG
*************************** 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_LOCKSG
*************************** 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_WAITSG
*************************** 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_LOCKSG
*************************** 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_WAITSG
*************************** 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)

          　　
  • 相关阅读:
    202103226-1 编程作业
    阅读任务
    1 20210309-1 准备工作
    20210405-1 案例分析作业
    第一周作业
    20210309-2 阅读任务
    20210309-1 准备工作
    编程作业
    阅读任务
    准备工作
  • 原文地址：https://www.cnblogs.com/maggie94/p/6749498.html
Copyright © 2020-2023  润新知