当数据量达到每秒10条的时侯,数据库开始出现了很麻烦的问题
M_MESSAGE是数据写入表
P_MESSAGE是数据处理结果表
A\B\C\D程序对M_MESSAGE进行操作(VC6.0程序构架)
最后的结果写入p_MESSAGE表
多个程序访问M_MESSAGE表
A进程负责往M_MESSAGE表里写数据(目前还无法得知是否全部成功写入)
-----------现在A进程达到了每秒10条的速度
B、C、D程序顺序对表进行查询、UPDATE工作
B程序调用DBO.getnext_msg对表进行查询,处理过程详看存储过程
C程序每隔一秒调用存储过程对P_MESSAGE进行扫描查看是否有需要处理的数据,如果有就连续处理,处理完去更新M_MESSAGE表里的状态字段。
当达到每秒多条的时侯
出现A、B两个新记录在瞬间入了M_MESSAGE表,程序进程处理结果先处理了B,而没有处理A,把B处理了2次,A就没有处理了。
D进程最后进行UPDATE工作的时侯,加了检测UPDATE是否成功还使用了获取SQL SERVER 2000处理RESULT的信息来
判断是否UPDATE成功
当A进程达到1秒十条的时侯,出现在这种棘手的问题
附存储过程:
CREATE PROCEDURE dbo.getnext_msg
@MaxProcessTime INT,
@MaxFailureCount INT
AS
DECLARE @NextMsg INT
SET @NextMsg = 0
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT TOP 1
@NextMsg=MO_MSG_NO
FROM M_MESSAGE
WHERE IS_VALID = 1 and
(PROCESS_STATUS = 1 or
(PROCESS_STATUS = 2 and DATEADD(second,@MaxProcessTime,PROCESS_DATE)<GETDATE()) or
(PROCESS_STATUS = 4 and FAILURE_COUNT<@MaxFailureCount))
ORDER BY MO_MSG_NO DESC
IF @NextMsg = 0
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
ELSE
BEGIN
UPDATE M_MESSAGE
SET PROCESS_STATUS = 2, PROCESS_DATE=GETDATE()
WHERE MO_MSG_NO = @NextMsg
END
COMMIT TRANSACTION
RETURN @NextMsg
GO
===========
CREATE PROCEDURE DBO.ServerNextMsg
@MaxProcessTime INT,
@MaxCheckTime INT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
declare @NextMsg as int
declare @service_id as varchar(10)
declare @src_id as varchar(15)
declare @dest_id as varchar(30)
select TOP 1 @NextMsg = P_MESSAGE.MSG_NO, @service_id = source_msg.service_id ,@src_id =P_MESSAGE.send_id from P_MESSAGE inner
join source_msg on P_MESSAGE.source_msg_id = source_msg.source_msg_id
where ((P_MESSAGE.PROCESS_STATUS = 1 or (P_MESSAGE.PROCESS_STATUS = 2 and DATEADD(second,@MaxProcessTime,P_MESSAGE.PROCESS_DATE)<GETDATE()))
and ( DATEADD(second,@MaxCheckTime,P_MESSAGE.CREATE_DATE)<GETDATE() )
and P_MESSAGE.IS_VALID = 1) and (source_msg.S_ID = '8983') ORDER by MSG_NO DESC
IF @NextMsg = 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
--ELSE IF @service_id not like 'BZ' and @service_id not like '269117' and @service_id not like '269115' and @service_id not like '269106' and @service_id not like '269107' and @service_id not like '269108' and @service_id not like '269118' and @NextMsg <> 0
ELSE IF @NextMsg <> 0 and @service_id not like 'BZ'
BEGIN
SELECT TOP 1 @dest_id = dest_id from m_message where m_message.src_id = @src_id and m_message.service_id = @service_id and m_message.process_status<>5 order by process_date desc
IF @dest_id is null OR @dest_id = ''
begin
ROLLBACK TRANSACTION
RETURN -2
end
else
begin
UPDATE P_MESSAGE
SET PROCESS_STATUS = 2, PROCESS_DATE=GETDATE()
WHERE MSG_NO = @NextMsg
--UPDATE m_message
--SET PROCESS_DATE=GETDATE(),MSG_NO = @NextMsg
--WHERE dest_id = @dest_id
end
END
ELSE
BEGIN
UPDATE P_MESSAGE
SET PROCESS_STATUS = 2, PROCESS_DATE=GETDATE()
WHERE MSG_NO = @NextMsg
END
COMMIT TRANSACTION
RETURN @NextMsg
GO