innodb 锁机制

最新推荐文章于 2024-08-11 13:37:35 发布
最新推荐文章于 2024-08-11 13:37:35 发布
阅读量91 收藏
点赞数
原文链接：http://www.cnblogs.com/maggie94/p/6749498.html
版权

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锁的兼容性
 XS
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_trxinnodb_locksinnodb_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)

        　　

转载于:https://www.cnblogs.com/maggie94/p/6749498.html

weixin_30414155
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Innodb 锁机制
夜未蓝的点滴记忆
08-18 981
一：什么是锁 锁是数据库系统的一个特性，用于管理队共享资源的并发访问；Myisam是表锁，Innodb行锁。
MySql--InnoDb锁机制要点总结
weixin_44580146的博客
10-15 196
1.InnoDB采用行锁机制，每次读写在对应行上添加X（排他）锁、S（共享）锁，以及在其上层表加一个意向锁（IX、IS）。但是意向锁除了全表查询基本不会起阻塞作用 2.查看事务、锁相关信息的mysql命令： ​ 1.select * from information_schema.INNODB_TRX查看事务状态 ​ 2.select * from information_schema.INNODB_LOCKS 查看锁状态 ​ 3.select * from information_schema.INNO.
Innodb锁机制
08-29 2173
InnoDB锁问题 InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。下面我们先介绍一点背景知识，然后详细讨论InnoDB的锁问题。 背景知识 1．事务（Transaction）及其ACID属性 事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称
InnoDB 锁机制
pointer_future的专栏
06-10 342
1. Locking Read 示例 场景一 (for share): 假设想要在一个名叫 child 的表中插入新 row, 前提要确保在名叫 parent 的表中包含一个 parent row. 想要确保引用的完整性, 可以按照如下步骤组织代码: 首先, 不能使用 noblocking read 读取 parent row 的方式, 检查 parent row 是否存在. 因为其他的事务可能在你 select 和 insert 操作之间, 把 parent row 删除了. 而你这个事务却意识不到
MySQL InnoDB锁机制深度剖析（乐观锁、悲观锁、共享锁、排它锁、行锁、表 锁、死锁）
最新发布
qq_51263863的博客
08-11 985
乐观锁适用于读多写少的场景，可以提高并发性能，但需要在应用层处理冲突。悲观锁适用于写多读少的场景，可以确保数据一致性，但可能影响并发性能。共享锁和排他锁是InnoDB实现并发控制的基础，理解它们有助于我们选择合适的查询方式。行锁提供了细粒度的并发控制，但要注意索引对行锁的影响。表锁在某些情况下是必要的，但应该谨慎使用，因为它会显著降低并发性。死锁是并发系统中的常见问题，需要通过合理的锁策略和事务设计来避免。间隙锁解决了幻读问题，但也可能导致某些操作被阻塞，需要在隔离性和并发性之间权衡。意向锁。
innodb锁机制
qq_45313714的博客
09-17 369
innodb中的锁机制原理解析 1.共享锁 S锁，读锁，多个事务共享一把锁，都能访问到数据，只读，不可修改。A 在读，B可读，但是不可写。 select * from count where id = 1 lock on share mode； 2.排他锁 X锁，写锁，不能与其他锁并存。只有当获取排他锁的事务处理完后，其 他操作方可进行。updata 自动加排他锁 -语句后加for update innodb行锁？锁的是什么？ 行锁就是给索引项加锁 ，否则就是表锁。 3.意向共享锁 IS： 表示事务准备给
mysql事务与锁机制（存储引擎和锁、MyISAM锁机制、InnoDB锁机制、Next-Key锁、Dead-Lock）.docx
07-24
#### 三、InnoDB锁机制 **1、事务基础概念** 事务是数据库操作的基本单位，确保数据的一致性和完整性。InnoDB支持ACID特性（原子性、一致性、隔离性、持久性），并通过多种锁机制来实现这些特性。 **2、锁机制...
InnoDB锁机制学习笔记
07-28
MySQL的引擎简介，InnoDB的锁机制与事务隔离级别
解析数据库锁协议和InnoDB的锁机制(全面解析行级锁、表级锁、排他锁、共享锁、悲观锁、乐观锁等常用锁)
12-14
前言 数据库通过锁以及锁协议来进行并发控制，解决并发事务带来的问题，本篇博文主要是解析数据库的锁协议和Mysql的默认存储引擎InnoDB的锁机制。 如果对事务隔离级别以及并发事务带来的问题不熟悉可以翻阅我的另外一篇博文–《解析事务隔离(事务隔离是如何解决脏读、幻读、不可重复读等问题)》 这篇文章中会涉及一些MVCC以及快照读、当前读的概念，如果不是很了解可以翻阅我另外一篇关于MVCC在InnoDB中实现原理的博文–《InnoDB的MVCC实现原理(InnoDB如何实现MVCC以及MVCC的工作机制)》。 锁协议 在介绍锁之前，我先介绍下锁协议，锁协议规定了何时加锁、释放锁的规则，不同的规则可
InnoDB 的锁机制
梦想的专栏
01-05 414
InnoDB 的锁机制 阅读 832 收藏 57 2016-11-25 原文链接：owl-pi.com 腾讯云域名限量秒杀中！.com低至28元，.club最低1元！立即了解详情抢购吧！https://dnspod.cloud.tencent.com/act/yearendsales 写在前面 使用数据库时，想要较高的吞吐、较低的延迟，但又想在高并发下可以
数据库概述09（数据库中的锁机制）
weixin_44251806的博客
10-12 402
锁机制
InnoDB引擎下的锁机制
qq_51210847的博客
03-05 446
InnoDB引擎下的锁机制：全局锁、表锁、行级锁、意向锁、读锁、排他锁（写锁）
Mysql InnoDB 的锁机制
凡夫编程
11-24 1139
处理数据并发问题时，绝不能想当然，搞清楚数据库是什么版本存储引擎？事务隔离级别是什么？读取操作加锁方式是什么？这些信息搞清楚，才是分析并发问题的第一步。
MySQL的InnoDB锁机制
daisy_xiu的专栏
09-19 925
在innodb的事务隔离级别是可重复读级别且innodb_locks_unsafe_for_binlog禁用的前提下，MYSQl的锁机制经过测试
MySQL模拟死锁以及分析和解决方案
泡^泡
08-20 4209
mysql死锁模拟案例、死锁的发生条件、怎么避免死锁、查看死锁日志以及解决方案
MySQL InnoDB锁机制解析
"辛星笔记之InnoDB锁" 在MySQL数据库中，InnoDB存储引擎是支持事务处理的主要引擎，它的锁机制对于确保数据的一致性和并发性至关重要。本笔记主要介绍了InnoDB锁的概念、详解、算法以及可能出现的问题。 **第一节...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值