下面是我之前写的的一个序列生成函数,后来发现了一个问题,在这里做个改进,并结合实验说明。
一、之前有问题的序列生成函数
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