mysql插入数据后返回自增ID的方法,5加主键锁读问题

MySQL5.5加主键锁读问题

mysql插入数据后返回自增ID的方法

mysql字符串链接与值设置

 

 

 

背景

mysql和oracle插入的时候有一个很大的区别是,oracle支持序列做id,mysql本身有一个列可以做自增长字段,mysql在插入一条数据后,如何能获得到这个自增id的值呢?

update `vip_guest` set invite_name =( 

     有同学讨论到MySQL 5.5下给大表加主键时会锁住读的问题,怀疑与fast
index creation有关,这里简单说明下。

 

 

 

方法一是使用last_insert_id

case when invite_name is null  then  NULL 

对照现象

 

 

         为了说明这个问题的原因,有兴趣的同学可以做对比实验。

1

case when invite_name is not null then  CONCAT(‘0’,invite_name) 

    1)  在给InnoDB表创建主键期间,会锁住该表上的读数据

mysql> SELECT LAST_INSERT_ID();

 

    2) 但是同样的表执行删除主键期间,不会锁住该表上的读操作

    产生的ID
每次连接后保存在服务器中。这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个
AUTO_INCREMENT值的。这个值不能被其它客户端影响,即使它们产生它们自己的
AUTO_INCREMENT值。这个行为保证了你能够找回自己的 ID
而不用担心其它客户端的活动,而且不需要加锁或处理。 

else “” 

—-这说明与是否fast index
creation无关,因为这两个操作在数据层面的行为应该是类似的,实际上,创建/删除主键都必须copy
data。

 

 

 

    每次mysql_query操作在mysql服务器上可以理解为一次“原子”操作,
写操作常常需要锁表的, 是mysql应用服务器锁表不是我们的应用程序锁表。

)

    3) 在创建主键期间,会锁住该表上执行的show create table

 

 

—-用1、3的现象可以猜测出,实际上与meta data lock有关。

   
值得注意的是,如果你一次插入了多条记录,这个函数返回的是第一个记录的ID值。

update `vip_guest` set
invite_name =( case when invite_name is null then NULL case when
invite_name is not null then CONCAT(0,invite_name) else )…

 

 

关于meta data lock(MDL)

   
因为LAST_INSERT_ID是基于Connection的,只要每个线程都使用独立的Connection对象,LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert
or
update*作生成的第一个record的ID。这个值不能被其它客户端(Connection)影响,保证了你能够找回自己的
ID
而不用担心其它客户端的活动,而且不需要加锁。使用单INSERT语句插入多条记录,
 LAST_INSERT_ID返回一个列表。

         MySQL 5.5中引入了MDL,当需要访问、修改表结构时,都需要对meta
data加锁(读或写)。比如,当一个线程需要修改表结构的任意一部分时,此时需要阻塞对表结构的访问,当然也需要阻塞对数据行的访问。

 

 

    LAST_INSERT_ID
是与table无关的,如果向表a插入数据后,再向表b插入数据,LAST_INSERT_ID会改变。

加主键流程

 

         当对一个表作加主键操作时,大致流程如下

方法二是使用max(id)

        1) MDL加写锁

 

       2) 操作数据,最耗时部分,注意需要copy data,因此流程上是

使用last_insert_id是基础连接的,如果换一个窗口的时候调用则会一直返回10

             a)创建一个临时表A,表A定义为修改后的表结构

 

             b)从原表读取数据插入表A

如果不是频繁的插入我们也可以使用这种方法来获取返回的id值

             c)删除原表,将表A重命名为原表名

 

       3)  MDL释放写锁

1

 

select max(id) from user;

从这个流程可以看到,在最耗时的部分,meta
data是被一个X锁保护的。因此在此期间,show create table 或者select
data都是会被阻塞。

这个方法的缺点是不适合高并发。如果同时插入的时候返回的值可能不准确。

 

 

这解释了上面的1) 3)。

方法三是创建一个存储过程,在存储过程中调用先插入再获取最大值的操作

 

 

删除主键流程

1

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图