mysql 用存储过程和函数分别模拟序列

在其他大部分DBMS里都有序列的概念，即Sequence或Generator。

而mysql里没有，但有时真的很有用。下面分别用存储过程和函数来模拟序列，并用程序模拟并发场景来测试原子性和完整性，是否能达到预期。

序列表定义如下：

CREATE TABLE `seq` (

    `id` BIGINT(20) NOT NULL,

    `busi` VARCHAR(50) NULL DEFAULT NULL,

    `val` BIGINT(20) NULL DEFAULT NULL,

    `remark` VARCHAR(50) NULL DEFAULT NULL,

    PRIMARY KEY (`id`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

;

先把模拟调用程序放这里，因为它是不变的：

            for (int j = ; j < ; j++)

            {

                Thread t1 = new Thread(() =>

                {

                    for (int i = ; i < ; i++)

                    {

                        using (var db = new DbCtxt())

                        {

                            //long val = db.Sql("select nextval();").QuerySingle<long>();

                            long val = db.Sql("call nextval();").QuerySingle<long>();

                            Console.WriteLine(val+" = "+Thread.CurrentThread.ManagedThreadId);

                        }

                    }

                });

                t1.Start();

            }

1、假设存储过程不加事务，读取时不for update

      declare v bigint ;

      -- start transaction;

      set v = (select val from seq where busi = 'mat');

      set v = v + 1;

      update seq set val = v where busi = 'mat';

      -- commit;

      select v;

测试结果：会出现并发读和写数据，现象就是预期序列增加1000，实际每次测试都是增加700~800不定。

2、假设存储过程不加事务，读取时加for update

      declare v bigint ;

      -- start transaction;

      set v = (select val from seq where busi = 'mat' for update);

      set v = v + 1;

      update seq set val = v where busi = 'mat';

      -- commit;

      select v;

测试结果：会出现并发读和写数据，现象就是预期序列增加1000，实际每次测试都是增加700~800不定。

3、假设存储过程加事务，读取时不加for update

      declare v bigint ;

      start transaction;

      set v = (select val from seq where busi = 'mat' );

      set v = v + 1;

      update seq set val = v where busi = 'mat';

      commit;

      select v;

测试结果：每次测试都会出现 Deadlock死锁，并且是很快（val增加不到100）就出现死锁。

4、假设存储过程加事务，读取时也加for update

      declare v bigint ;

      start transaction;

      set v = (select val from seq where busi = 'mat' for update);

      set v = v + 1;

      update seq set val = v where busi = 'mat';

      commit;

      select v;

测试结果：多线程读取，预期序列增加1000，实际每次测试都增加1000，符合预期。

5、用函数模拟，函数不允许显示或隐式的开启事务，先测试读取时不加for update

      declare v bigint ;

      -- start transaction;

      set v = (select val from seq where busi = 'mat' );

      set v = v + 1;

      update seq set val = v where busi = 'mat';

      -- commit;

      return v;

测试结果：多线程读取，预期序列增加1000，实际每次测试都增加1000，符合预期。

6、用函数模拟，测试读取时加for update

      declare v bigint ;

      -- start transaction;

      set v = (select val from seq where busi = 'mat' for update);

      set v = v + 1;

      update seq set val = v where busi = 'mat';

      -- commit;

      return v;

测试结果：多线程读取，预期序列增加1000，实际每次测试都增加1000，符合预期。

总结：若用函数模拟最为简单，不用考虑是否锁定行for update，调用方式 select nextval();

若用存储过程模拟，必须要考虑锁定行for update，且多sql前后要加事务管理，调用方式 call nextval();

巴特西

mysql 用存储过程和函数分别模拟序列

最新文章

热门文章