首页 > 数据库 >MySQL高级锁之自增锁

MySQL高级锁之自增锁

时间:2023-07-18 22:33:10浏览次数:37  
标签:insert 自增锁 into 高级 id 插入 MySQL 增锁 t4

2.5 自增锁

MySQL的自增锁是指在使用自增主键(Auto Increment)时,为了保证==唯一性和正确性==,系统会对自增字段进行加锁。这样可以确保同时插入多条记录时,每条记录都能够获得唯一的自增值。

  • 创建测试表:
drop table if exists t3;

CREATE TABLE `t3`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=1;

insert into t3 values(1,20);
insert into t3 values(2,25);

2.5.1 表的插入数据方式

我们之前在表中插入数据都是用最基本的insert,但insert语句的用法用很多,另外MySQL还提供replace语句,运行对表中的数据进行替换;

  • insert用法:
drop table if exists t4;

CREATE TABLE `t4`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=1;

-- 插入记录,如果存在这条记录就报错(主键唯一)
insert into t4 values(10,20);
insert into t4 values(11,20),(12,21),(13,22);
insert into t4 set id=14,age=25;
insert into t4 select * from t3;
  • replace用法:
delete from t4;

-- 如果没有这条记录就新增,有这条记录就修改
replace into t4 values(1,20);  
replace into t4 set id=10,age=100 ;
replace into t4 select * from t3;

2.5.1 insert的不同类型

1)Simple inserts

简单插入模式

  • 示例:
insert into table_name values(xxx);
  • 特点:可以提前确定要插入的行数
2)Bulk inserts

批量插入模式,包含insert...select、replace select、load data等语句;

  • 示例:
insert into t4 select * from t3;
replace into t4 select * from t3;

Tips:load data属于海量数据插入,暂时不演示

  • 特点:事先不知道要插入的行数,以及所需的自动增量值的数量
3)Mixed-mode

混合插入模式

  • 示例:
insert into table_name values(xxxx),(xxxx),(xxxx);
  • 特点:为一些(但不是全部)新行指定自动增量值

2.5.2 自增锁原理

1)插入原理

MySQL自增锁的实现机制是使用了一个名为"auto-increment lock"的互斥锁。当使用INSERT语句插入一条新记录时,MySQL会自动为自增字段加锁,防止其他并发的插入操作同时获取相同的自增值。这个锁是在内部实现的,不需要用户手动创建或管理。

自增锁确保了插入记录的唯一性和正确性,避免了并发插入产生冲突。但同时也会带来一些性能上的影响,因为并发插入操作需要等待锁的释放。因此,在高并发的场景下,可能需要考虑使用其他方案来避免自增锁成为瓶颈。

MySQL高级锁之自增锁_mysql

注意:自增锁跟事务无关,即使多个insert语句存在同一个事务中,每次insert都会申请最新的自增锁来获取最新的AUTO_INCREMENT值;自增锁保持到insert语句结束,而不是事务结束;

2)自增锁表锁

需要注意的是,自增锁是基于表级别的,而不是行级别的。这意味着在同一时刻针对于同一张表只能有一个线程在插入记录(前提是需要increment来分配id),并且每个表都有一个自己独立的自增锁。

注意:自增锁的"表级别"指的是每张表都有自己的自增锁,而不是指获取自增锁的时候会把整个表锁起来;

MySQL高级锁之自增锁_乐观锁_02

2.5.3 自增锁的模式

和自增锁相关的一个参数为(5.1.22版本之后加入)innodb_autoinc_lock_mode:可以设定3个值,0,1,2

show variables like 'innodb_autoinc_lock_mode';

MySQL高级锁之自增锁_自增锁_03

  • 0:traditional(传统模式)
  • 1:consecutive(连续模式)
  • 2:interleaved(交叉模式)

Tips:参数只控制InnoDB引擎的设置,所有MyISAM均为traditional ,每次均会进行表锁。只有Innodb会视参数不同而产生不通的锁。

1)traditional(传统模式)

在传统模式下,不管是Simple inserts还是Bulk inserts时每个insert获取自增锁锁时都会触发表锁,在某个insert没有释放表锁之前其他线程/进程均不可获取自增锁;虽然传统模式保证了多个insert插入的连续性但实际上并发插入属于串行化,性能较低;

MySQL高级锁之自增锁_自增锁_04

2)consecutive(连续模式)

在连续模式下,InnoDB会根据当前执行的insert语句来判断是否使用表级别自增锁。这也是InnoDB的默认值;

  • Simple inserts:InnoDB能够预先知道要插入的行数,因此产生的自增锁只会锁住对应的那些id(页锁),避免表级别的自增锁
  • Bulk Inserts:InnoDB无法预知要插入的行,触发表级别自增锁

【Simple Inserts】

MySQL高级锁之自增锁_mysql_05

【Bulk Inserts】

MySQL高级锁之自增锁_表锁_06

3)interleaved(交叉模式)

在交叉模式下,所有的insert语句都不会使用自增锁(悲观锁),而是采用一个轻量级的mutex乐观锁,来一个insert立即处理,在生成insert语句完毕后检查id是否被其他线程/进程使用,如果已经被使用则重新获取id;交叉模式并发量最高,但对于同一个语句来说它所得到的auto_increment值可能不是连续的。

MySQL高级锁之自增锁_排它锁_07

Tips:参数只控制InnoDB引擎的设置,所有MyISAM均为traditional ,每次均会进行表锁。只有Innodb会视参数不同而产生不通的锁。

标签:insert,自增锁,into,高级,id,插入,MySQL,增锁,t4
From: https://blog.51cto.com/lscl/6769169

相关文章

  • PyMySQL插入字典类型的数据
    importpymysqlMYSQL_CONFIG={'host':'localhost',#IP地址'port':3306,#端口'user':'root',#用户名'passwd':'123456',#密码'db':'layui',......
  • Ubuntu安装MySQL、nginx、nacos
    Ubuntu安装mysql1、安装mysql#此命令直接安装最新版mysql8.0sudoapt-getinstallmysql-server2、查看默认用户及密码cat/etc/mysql/debian.cnfuser =debian-sys-maintpassword =s8hjWltF0W2EBU323、登录该用户mysql-udebian-sys-maint-pusemysql;updateuse......
  • mysql text 长度定义
    MySQLText字段长度定义作为一名经验丰富的开发者,我将教你如何实现“MySQLText字段长度定义”。下面我将分步骤向你介绍整个过程,并附上相应的代码示例。步骤步骤说明1创建数据库表2设计Text字段3定义Text字段的长度步骤1:创建数据库表首先,我们需要创建一......
  • mysql 事务自动回滚
    MySQL事务自动回滚在MySQL数据库中,事务是一组原子性操作的集合,它们要么全部成功执行,要么全部失败回滚。事务可以保证在并发环境下数据的一致性和完整性。当一个事务执行出现错误或异常时,数据库会自动回滚到事务开始之前的状态,保证数据的完整性。事务的基本概念在MySQL中,事务由以......
  • mysql 事务和存储过程
    MySQL事务和存储过程1.事务的概念和流程事务是指一组数据库操作,它们作为一个逻辑单元一起执行,并且要么全部都执行成功,要么全部都回滚到事务开始前的状态。MySQL中的事务主要用于保证数据的完整性和一致性。以下是MySQL事务的基本流程:步骤描述1开启事务2执行SQL......
  • mysql 事务当中为什么出现幻读
    MySQL事务中为什么出现幻读1.介绍在MySQL数据库中,事务是一组原子性的操作,可以确保数据的一致性和完整性。然而,在一些特定情况下,即使使用了事务,仍然可能出现幻读的问题。幻读指的是在一个事务中,当对某个表进行查询时,其他事务在这个事务的锁尚未释放之前,插入了新的数据,导致前一个......
  • mysql 使用查询结果update
    MySQL使用查询结果更新的流程在MySQL中,我们可以使用查询结果来更新数据。下面是实现这个过程的步骤表格:步骤描述第一步连接到MySQL数据库第二步编写查询语句第三步执行查询语句第四步获取查询结果第五步编写更新语句第六步执行更新语句下面我......
  • mysql 时间加5分钟
    MySQL时间加5分钟MySQL是一种流行的关系型数据库管理系统,广泛用于各种应用程序的数据存储和处理。在MySQL中,我们经常需要对日期和时间进行计算和操作。本文将介绍如何在MySQL中将时间加5分钟,以及提供相应的代码示例。使用DATE_ADD函数在MySQL中,我们可以使用DATE_ADD函数来对时间......
  • mysql 时间范围
    MySQL时间范围MySQL是一个广泛使用的关系型数据库管理系统,它提供了丰富的功能来处理和操作数据。在许多应用中,我们经常需要根据时间范围来查询和分析数据。本文将介绍如何在MySQL中使用时间范围来查询数据,并提供一些代码示例来帮助您更好地理解。时间和日期类型在MySQL中,......
  • mysql 设置主键自增的步长
    MySQL设置主键自增的步长在MySQL数据库中,主键是用于唯一标识表中每一行数据的字段。主键自增是一种常见的设置,它可以自动为主键字段生成递增的值,简化了开发过程和数据维护的复杂性。本文将介绍如何在MySQL中设置主键自增的步长。什么是主键自增主键自增是一种机制,它使用一个特殊......