奇偶表解决表并发问题

USE [ProductDB]
GO

/****** Object:  StoredProcedure [dbo].[sp0_ProductCacheCreate2]    Script Date: 12/12/2012 15:46:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp0_ProductCacheCreate2]
AS
BEGIN
 --操作Product_Cache2表
 
 --删除Product_Cache2表的数据
 TRUNCATE TABLE Product_Cache2
 
 --生成产品表的有效排期
 DECLARE @ProductId int;
 DECLARE @EffectDate datetime;
    DECLARE @ExpireDate datetime;
    DECLARE @AdvanceDays int;
   
 DECLARE @TODAY datetime;
 SELECT @TODAY=CONVERT(varchar(10),GETDATE(),23);

    DECLARE #temp_cursor CURSOR FOR       
  SELECT ProductId, EffectDate,[ExpireDate], AdvanceDays FROM Product
  WHERE IsUp=1 AND IsValid=1 AND ProductType=1 AND [ExpireDate]>=DATEADD(d, AdvanceDays, @TODAY);
      
    OPEN #temp_cursor
    FETCH NEXT FROM #temp_cursor INTO @ProductId,@EffectDate,@ExpireDate,@AdvanceDays
 WHILE @@FETCH_STATUS = 0
  BEGIN
  DECLARE @PriceDate datetime;
  DECLARE @MinPrice int;
  
  IF @EffectDate>=DATEADD(d, @AdvanceDays, @TODAY)
   SET @PriceDate=@EffectDate;
  ELSE
   SET @PriceDate=DATEADD(d, @AdvanceDays, @TODAY);
   
  WHILE @PriceDate<=@ExpireDate
   BEGIN
    --获取最低价，所有规格和有效日期
    SELECT @MinPrice=MIN(Price) FROM Product_Price WHERE ProductId=@ProductId AND (EffectDate<=@PriceDate AND [ExpireDate]>=@PriceDate)
    IF @MinPrice>0
    BEGIN
     INSERT Product_Cache2 (ProductId, PriceDate, MinBasisPrice) VALUES(@ProductId, @PriceDate, @MinPrice)
     SET @PriceDate=DATEADD(d, 1, @PriceDate);
    END
    ELSE
    BEGIN
     --跳跃前进，重新定义开始日期
     SELECT @PriceDate=Min(EffectDate) FROM Product_Price WHERE ProductId=@ProductId AND EffectDate>@PriceDate
     IF @PriceDate IS NULL
      BREAK;
    END
   END

  FETCH NEXT FROM #temp_cursor INTO @ProductId,@EffectDate,@ExpireDate, @AdvanceDays
  END
  CLOSE #temp_cursor
  DEALLOCATE #temp_cursor
   
  --删除关闭日期
  DELETE Product_Cache2 WHERE EXISTS (SELECT DepartureDate FROM Product_NoDeparture B WHERE Product_Cache2.ProductId=B.ProductId AND Product_Cache2.PriceDate=B.DepartureDate)
END
GO

/****** Object:  StoredProcedure [dbo].[sp0_ProductCacheCreate1]    Script Date: 12/12/2012 15:46:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp0_ProductCacheCreate1]
AS
BEGIN
 --操作Product_Cache1表
 
 --删除Product_Cache1表的数据
 TRUNCATE TABLE Product_Cache1
 
 --生成产品表的有效排期
 DECLARE @ProductId int;
 DECLARE @EffectDate datetime;
    DECLARE @ExpireDate datetime;
       
    DECLARE @LastProductId datetime;   
    DECLARE @PriceDate datetime;
  
    --以Product_Price表为中心，注意排序
    DECLARE #temp_cursor CURSOR FOR
 SELECT ProductId,MathEffectDate,MathExpireDate FROM
 (
 SELECT A.ProductId,
 CONVERT(varchar(10),CASE WHEN (CASE WHEN A.EffectDate <= B.EffectDate THEN B.EffectDate ELSE A.EffectDate END) >=
 DATEADD(d, B.AdvanceDays, GETDATE())
 THEN (CASE WHEN A.EffectDate <= B.EffectDate THEN B.EffectDate ELSE A.EffectDate END)
 ELSE DATEADD(d, B.AdvanceDays,GETDATE()) END, 23) AS MathEffectDate,
 (CASE WHEN A.[ExpireDate] >= B.[ExpireDate] THEN B.[ExpireDate] ELSE A.[ExpireDate] END) AS MathExpireDate
 FROM Product_Price AS A INNER JOIN Product AS B ON A.ProductId = B.ProductId
 WHERE (B.IsUp = 1) AND (B.IsValid = 1) AND (B.ProductType = 1)
 AND A.[ExpireDate] >= DATEADD(d, B.AdvanceDays, CONVERT(varchar(10),GETDATE(),23))
 AND B.[ExpireDate] >= DATEADD(d, B.AdvanceDays, CONVERT(varchar(10),GETDATE(),23))
 AND A.EffectDate <= B.[ExpireDate] AND A.[ExpireDate] >= B.EffectDate
 ) C
 GROUP BY ProductId, MathEffectDate, MathExpireDate
 ORDER BY ProductId, MathEffectDate, MathExpireDate 
      
    OPEN #temp_cursor
    FETCH NEXT FROM #temp_cursor INTO @ProductId,@EffectDate,@ExpireDate
 WHILE @@FETCH_STATUS = 0
 BEGIN
  DECLARE @MinPrice int;
  IF @ProductId = @LastProductId
  BEGIN
   IF @PriceDate<@EffectDate
    SET @PriceDate=@EffectDate;
  END
  ELSE
   SET @PriceDate=@EffectDate;  
   
  WHILE @PriceDate<=@ExpireDate
  BEGIN
   --获取最低价，所有规格和有效日期
   SELECT @MinPrice=MIN(Price) FROM Product_Price WHERE ProductId=@ProductId AND (EffectDate<=@PriceDate AND [ExpireDate]>=@PriceDate)
   INSERT Product_Cache1 (ProductId, PriceDate, MinBasisPrice) VALUES(@ProductId, @PriceDate, @MinPrice)
   SET @PriceDate=DATEADD(d, 1, @PriceDate);      
  END
  SET @LastProductId=@ProductId;
  
  FETCH NEXT FROM #temp_cursor INTO @ProductId,@EffectDate,@ExpireDate
 END
 CLOSE #temp_cursor
 DEALLOCATE #temp_cursor
   
 --删除关闭日期
 DELETE Product_Cache1 WHERE EXISTS (SELECT DepartureDate FROM Product_NoDeparture B WHERE Product_Cache1.ProductId=B.ProductId AND Product_Cache1.PriceDate=B.DepartureDate)

 --算出MinAttachPrice，每个行程段的机票/酒店最低价
 --建立专门计算资源价格的sp?
 --SELECT * FROM Product_Attach_Product
 --SELECT * FROM Product WHERE ProductType=4
 --取一级即可
END
GO

/****** Object:  StoredProcedure [dbo].[sp0_ProductCacheCreate]    Script Date: 12/12/2012 15:46:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp0_ProductCacheCreate]
AS
BEGIN
 /*
 建立两个表，三个SP
 表名：Product_Cache1、Product_Cache2
 sp0名：sp0_ProductCacheCreate、sp0_ProductCacheCreate1、sp0_ProductCacheCreate2

 sp0_ProductCacheCreate的逻辑：
 为了防止sp没有执行完，则当前分钟数>55分钟，则等待到下一个小时数再执行。（最好初次执行时在前30分钟内）
 当前小时数为奇数时，调用sp0_ProductCacheCreate2（交叉执行）
 当前小时数为藕数时，调用sp0_ProductCacheCreate1（交叉执行）

 JOB:
 每小时执行一次

 客户端调用逻辑：
 当前小时数为奇数，查询Product_Cache1表
 当前小时数为藕数，则查询Product_Cache2表
 */
 
 --IF DATEPART(hh,getdate())/10=1
 --BEGIN
  --SELECT 'JOB每隔10分钟执行一次'
 --END

 IF DATEPART(mi, getdate())>55
 BEGIN
  --SELECT 'JOB每隔小时执行一次'
  WAITFOR DELAY '00:05:00';
 END

 IF DATEPART(hh,getdate())%2=1
  BEGIN
   --SELECT 'sp0_ProductCacheCreate2'
   EXEC sp0_ProductCacheCreate2
  END
 ELSE
  BEGIN
   --SELECT 'sp0_ProductCacheCreate1'
   EXEC sp0_ProductCacheCreate1
  END
END
GO

 

==============================

表并发解决方案：
1.两点变多点，提前多个访问，永远不会出错。
2.奇偶问题，解决时间交插点问题（定在时间点10分钟执行，每小时执行一次）

3。临时表，先insert临时表，成功后再一个事务将期到正式表，长事务变为短事务。