有个表如下:CREATE TABLE [dbo].[T_FillingRec](
[FillingRec] [uniqueidentifier] NOT NULL,
[Organize] [varchar](16) NULL,
[SerialPort] [int] NULL,
[Sequence] [bigint] NULL,
[BUTime] [datetime] NULL,
[BUUserName] [varchar](32) NULL,
CONSTRAINT [T_Gas_FillingRec_PK] PRIMARY KEY CLUSTERED
(
[FillingRec] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 其主键是[FillingRec] [uniqueidentifier] ,每次都是New一个GUID出来。
这个表是存储多个下位机传过来的数据的一个表,每个下位机占用一个串口SerialPort,每条记录的Sequence加一,但是当上位机没有给下位机应答的时候,下位机会重复传输这条记录,也就是Sequence有可能传一致的,因此这个表实际上必须根据SerialPort和Sequence作为主键是最合适的,但是表结构就是这样了,不好修改。
上位机使用了完成端口的方式处理下位机传输过来的数据,因此在同一个Sequence的记录中,可能会有不同的线程在处理数据写入的工作,可能是由于不同记录中的事务问题,导致极其偶尔的同一个串口有重复Sequence的记录存在。
请问如何解决这个问题?
现在的存储过程如下:Create PROCEDURE [dbo].[pro_T_FillingRec1_Insert]
@FillingRec uniqueidentifier,
@Organize varchar(16),
@SerialPort int,
@Sequence bigint,
@BUTime datetime,
@BUUserName varchar(32)
ASSET NOCOUNT ONIF EXISTS(SELECT * FROM [dbo].[T_FillingRec1] WHERE SerialPort = @SerialPort And Sequence = @Sequence)
BEGIN
UPDATE [dbo].[T_FillingRec1] SET
[Organize] = @Organize,
[BUTime] = @BUTime,
[BUUserName] = @BUUserName
WHERE
SerialPort = @SerialPort And Sequence = @Sequence
END
ELSE
BEGIN
Set @FillingRec = NEWID()
INSERT INTO [dbo].[T_FillingRec1]
([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])
Select @FillingRec,@Organize,@SerialPort,@Sequence,@BUTime,@BUUserName
END
请问这个存储过程有什么地方可以改进的?
[FillingRec] [uniqueidentifier] NOT NULL,
[Organize] [varchar](16) NULL,
[SerialPort] [int] NULL,
[Sequence] [bigint] NULL,
[BUTime] [datetime] NULL,
[BUUserName] [varchar](32) NULL,
CONSTRAINT [T_Gas_FillingRec_PK] PRIMARY KEY CLUSTERED
(
[FillingRec] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 其主键是[FillingRec] [uniqueidentifier] ,每次都是New一个GUID出来。
这个表是存储多个下位机传过来的数据的一个表,每个下位机占用一个串口SerialPort,每条记录的Sequence加一,但是当上位机没有给下位机应答的时候,下位机会重复传输这条记录,也就是Sequence有可能传一致的,因此这个表实际上必须根据SerialPort和Sequence作为主键是最合适的,但是表结构就是这样了,不好修改。
上位机使用了完成端口的方式处理下位机传输过来的数据,因此在同一个Sequence的记录中,可能会有不同的线程在处理数据写入的工作,可能是由于不同记录中的事务问题,导致极其偶尔的同一个串口有重复Sequence的记录存在。
请问如何解决这个问题?
现在的存储过程如下:Create PROCEDURE [dbo].[pro_T_FillingRec1_Insert]
@FillingRec uniqueidentifier,
@Organize varchar(16),
@SerialPort int,
@Sequence bigint,
@BUTime datetime,
@BUUserName varchar(32)
ASSET NOCOUNT ONIF EXISTS(SELECT * FROM [dbo].[T_FillingRec1] WHERE SerialPort = @SerialPort And Sequence = @Sequence)
BEGIN
UPDATE [dbo].[T_FillingRec1] SET
[Organize] = @Organize,
[BUTime] = @BUTime,
[BUUserName] = @BUUserName
WHERE
SerialPort = @SerialPort And Sequence = @Sequence
END
ELSE
BEGIN
Set @FillingRec = NEWID()
INSERT INTO [dbo].[T_FillingRec1]
([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])
Select @FillingRec,@Organize,@SerialPort,@Sequence,@BUTime,@BUUserName
END
请问这个存储过程有什么地方可以改进的?
我的文章,希望可以帮到你:http://blog.csdn.net/dba_huangzj/article/details/7699166
问题不是出在NewID,我判断这条记录是否被传递了的时候,使用的不是uniqueidentifier
IF EXISTS(SELECT * FROM [dbo].[T_FillingRec1] WHERE SerialPort = @SerialPort And Sequence = @Sequence)
而是我想要识别的SerialPort和Sequence 我认为出现这个问题的关键因素是服务程序存在两条一样的记录同一个时间在尝试写入,而先提交的那个事务还未完成,所以后提交的那个查不到这条记录已经存在,就重新Insert了一条
--如果你的系统是2008的,那么使用merge将是完美方案
IF EXISTS(SELECT * FROM [dbo].[T_FillingRec1] WHERE SerialPort = @SerialPort And Sequence = @Sequence)
BEGIN
UPDATE [dbo].[T_FillingRec1] SET
[Organize] = @Organize,
[BUTime] = @BUTime,
[BUUserName] = @BUUserName
WHERE
SerialPort = @SerialPort And Sequence = @Sequence
END
ELSE
BEGIN
Set @FillingRec = NEWID()
INSERT INTO [dbo].[T_FillingRec1]
([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])
Select @FillingRec,@Organize,@SerialPort,@Sequence,@BUTime,@BUUserName
ENDMERGE dbo.[T_FillingRec1] AS Target
USING (SELECT SerialPort, Sequence,[BUTime],[BUUserName],[Organize] from xxx /*所有的新值*/) AS Source
ON (Target.SerialPort = Source.SerialPort AND Target.Sequence = Source.Sequence)
WHEN MATCHED THEN
UPDATE SET Target.[BUTime] = Source.[BUTime], Target.[BUUserName] = Source.[BUUserName], Target.[Organize] = Source.[Organize],
WHEN NOT MATCHED BY TARGET THEN
INSERT ([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])
VALUES (Source.[FillingRec], Source.[Organize], Source.[SerialPort], Source.[Sequence], Source.[BUTime], Source.[BUUserName]);
MERGE dbo.[T_FillingRec1] AS Target
USING (SELECT SerialPort, Sequence,[BUTime],[BUUserName],[Organize] from xxx /*所有的新值*/) AS Source
ON (Target.SerialPort = Source.SerialPort AND Target.Sequence = Source.Sequence)
WHEN MATCHED THEN
UPDATE SET Target.[BUTime] = Source.[BUTime], Target.[BUUserName] = Source.[BUUserName], Target.[Organize] = Source.[Organize],
WHEN NOT MATCHED BY TARGET THEN
INSERT ([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])
VALUES (Source.[FillingRec], Source.[Organize], Source.[SerialPort], Source.[Sequence], Source.[BUTime], Source.[BUUserName]);
这个不应该是输入参数应该在存储过程内部定义ASdeclare @FillingRec uniqueidentifierset @FillingRec =newid()
这样直接去更新,返回受影响的行数,行数为0就是不满足条件
然后就insert Create PROCEDURE [dbo].[pro_T_FillingRec1_Insert]
@FillingRec uniqueidentifier,
@Organize varchar(16),
@SerialPort int,
@Sequence bigint,
@BUTime datetime,
@BUUserName varchar(32)
ASSET NOCOUNT ON
declare @FillingRec uniqueidentifierset @FillingRec =newid()
UPDATE [dbo].[T_FillingRec1] SET
[Organize] = @Organize,
[BUTime] = @BUTime,
[BUUserName] = @BUUserName
WHERE
SerialPort = @SerialPort And Sequence = @Sequence
if @@rowcount=0
INSERT INTO [dbo].[T_FillingRec1]
([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])
Select @FillingRec,@Organize,@SerialPort,@Sequence,@BUTime,@BUUserName这样是最佳的,你这个情况用merge 不太合适,即使用楼上写得也有点问题
MERGE dbo.[T_FillingRec1] AS Target
USING (SELECT * from T_FillingRec1 WHERE
SerialPort = @SerialPort And Sequence = @Sequence
) AS Source
ON (Target.SerialPort = Source.SerialPort AND Target.Sequence = Source.Sequence)
WHEN MATCHED THEN
UPDATE 。
WHEN NOT MATCHED BY TARGET THEN
INSERT ;
另一种方法是先将数据存放在临时表,不用考虑是否重复。然后利用自动作业或者DTS或者ETL工具,通过代码将不重复的记录插入正式表中。
这是一个方法,例如我把主键改了,但是我想寻找其它的解决办法,因为还涉及不同站点的同步问题,所以主键使用GUID比较好点。
建一个unique约束 请问怎么约束?是对SerialPort、Sequence的约束吗?事务的隔离级别 这个又如何实现?