問題描述

嘗試通過建立序列表與函數(shù)實(shí)現(xiàn)，可以正常返回下條序列，但通過jmeter測試并發(fā)100個(gè)線程，發(fā)現(xiàn)返回的結(jié)果中存在重復(fù)的記錄。

下面是鏈接中的實(shí)現(xiàn)方法

-- 表CREATE TABLE `sequence_data` ( `sequence_name` varchar(100) NOT NULL, `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1, `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1, `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615, `sequence_cur_value` bigint(20) unsigned DEFAULT 1, `sequence_cycle` boolean NOT NULL DEFAULT FALSE, PRIMARY KEY (`sequence_name`));-- 函數(shù)delimiter //CREATE FUNCTION `nextval` (`seq_name` varchar(100))RETURNS bigint(20) NOT DETERMINISTICBEGIN DECLARE cur_val bigint(20); SELECTsequence_cur_value INTO cur_val FROMsequence_data WHEREsequence_name = seq_name ; IF cur_val IS NOT NULL THENUPDATE sequence_dataSET sequence_cur_value = IF ((sequence_cur_value + sequence_increment) > sequence_max_value,IF ( sequence_cycle = TRUE, sequence_min_value, NULL),sequence_cur_value + sequence_increment )WHERE sequence_name = seq_name; END IF; RETURN cur_val;END//delimiter ;-- insert new sequence-- INSERT INTO sequence_data (sequence_name) VALUE (’sq_my_sequence’);-- 調(diào)用-- SELECT nextval(’sq_my_sequence’) as nextval

問題解答

mysql默認(rèn)隔離級別是repeatable_read,在你事務(wù)提交之前，其他線程讀不到你的修改。可以在結(jié)尾增加commit提交update的結(jié)果。如果還不行就使用行級鎖(select * sequence_data from WHERE sequence_name = seq_name for update)，一個(gè)線程不提交，其他線程都不允許讀，這樣就一定可以保證并發(fā)安全。