MySQL 序列生成函数

最新推荐文章于 2024-07-31 11:30:39 发布

kf_panda

最新推荐文章于 2024-07-31 11:30:39 发布

阅读量3.8k

点赞数

分类专栏： MySQL 存储过程 & 函数 & 触发器

本文链接：https://blog.csdn.net/gua___gua/article/details/47397967

版权

本文探讨了MySQL序列生成函数的问题，指出在并发环境下可能出现的死锁错误，并提出了两种修改后的函数实现方式。通过实验验证，改进后的函数在不同隔离级别下均实现了并发安全。建议在使用序列时，一次性获取多个值以提高效率，同时不推荐在数据库中实现currval功能。

摘要由CSDN通过智能技术生成

下面是我之前写的的一个序列生成函数，后来发现了一个问题，在这里做个改进，并结合实验说明。

一、之前有问题的序列生成函数

drop table if exists my_sequence;  
    create table my_sequence (  
            seq_name        VARCHAR(50) NOT NULL comment '序列名称',   
            val     BIGINT UNSIGNED        NOT NULL comment '当前值',   
            PRIMARY KEY (seq_name)  
    )ENGINE=Innodb DEFAULT CHARSET=utf8;

	
drop function if exists fun_seq_nextval;
delimiter //
set global log_bin_trust_function_creators=true;
create function fun_seq_nextval (v_seq_name varchar(50),v_inc_num  bigint unsigned) 
returns bigint unsigned
begin
    declare v_cur bigint unsigned;
    
    select val into v_cur from my_sequence where seq_name = v_seq_name for update;
    
    if(v_cur IS NULL)
    then
        insert into my_sequence values (v_seq_name,0);
        set v_cur = 0;
    end if;
    update my_sequence  set val = val + v_inc_num   where seq_name = v_seq_name;
    
    if (v_inc_num=0)
    then
        return v_cur;
    else
        return v_cur+1;
    end if;
    
end;
//
delimiter ;

说明：上面的序列生成函数，如果查询的序列不存在，它会自动建立查询的序列，并返回值。

存在的问题

select val into v_cur from my_sequence where seq_name = v_seq_name for update;这条语句如果查询是空的时候会加X锁，但是不会等待（即就是多个回话都来得到这个空的seq_name的x锁的时候，都不会等待，但当真后者真正插入数据时会有等待）。这个时候造成的结果是，
当my_sequence中没有要查询到序列时，在并发情况下取同一个序列时，都会得到那个序列的X锁，然后都进入if条件，这个时候就报死锁的错误。

实验：验证多个for update查询空的seq_name不会等待

session1:                                                                      session2:

mysql> set autocommit=0;                                                       mysql> set autocommit=0;


mysql> select  seq_name  from my_sequence where seq_name = 'huang' for update;
Empty set (0.00 sec)

                                                                                mysql> select  seq_name  from my_sequence where seq_name = 'huang' for update;
                                                                                Empty set (0.00 sec)


session3查询锁状态:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
                    trx_id: AEEAD
                 trx_state: RUNNING
               trx_started: 2015-09-17 17:26:06
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 99282
                 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
*************************** 2. row ***************************
                    trx_id: AEE30
                 trx_state: RUNNING
               trx_started: 2015-09-17 17:24:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 99208
                 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
2 rows in set (0.00 sec)

Empty set (0.00 sec)

Empty set (0.00 sec)


												session2:
												mysql> insert into my_sequence values('huang',1);


session3查询锁状态:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
                    trx_id: AEEAD
                 trx_state: LOCK WAIT
               trx_started: 2015-09-17 17:26:06
     trx_requested_lock_id: AEEAD:0:2682:1
          trx_wait_started: 2015-09-17 17:26:55
                trx_weight: 3
       trx_mysql_thread_id: 99282
                 trx_query: insert into my_sequence values('huang',1)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 376
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ