单号重复触发问题解决

利用Sql server 悲观锁定原理，加事务TRAN和锁定with (UPDLOCK)

 

BEGIN TRAN
DECLARE      @max_bom_id             BIGINT
SELECT  @max_bom_id=ISNULL(CONVERT(BIGINT,param_value),201000000000)+1
      FROM pln_klsz_calculate_param_def with (UPDLOCK)
      WHERE param_type=10 AND param_id=10101015
      
--  延迟秒，模拟并发访问.
        waitfor delay '000:00:10'

   --更新最大的bom_id
   UPDATE pln_klsz_calculate_param_def
   SET param_value=@max_bom_id,
       update_time=dbo.ufnGetDateTime(getdate()),
       update_userid='admin'
   WHERE param_type=10 AND param_id=10101015      

COMMIT TRAN

SELECT param_value FROM pln_klsz_calculate_param_def
WHERE param_type=10 AND param_id=10101015

窗口2：
BEGIN TRAN
DECLARE      @max_bom_id             BIGINT
SELECT  @max_bom_id=ISNULL(CONVERT(BIGINT,param_value),201000000000)+1
      FROM pln_klsz_calculate_param_def with (UPDLOCK)
      WHERE param_type=10 AND param_id=10101015

   --更新最大的bom_id
   UPDATE pln_klsz_calculate_param_def
   SET param_value=@max_bom_id,
       update_time=dbo.ufnGetDateTime(getdate()),
       update_userid='admin'
   WHERE param_type=10 AND param_id=10101015      

COMMIT TRAN

SELECT param_value FROM pln_klsz_calculate_param_def
WHERE param_type=10 AND param_id=10101015