One part of the investigations this morning has been looking into how we manage the database access as we add parts of an incoming message to it for later processing. As we have multiple servers processing inbound messages, there was the potential for two threads trying to write to the database simultanously with different parts of the same message. Both could look at the database and think there were no existing parts of a multipart message, and decide to try and insert a new multipart message record.
A quick mockup test yesterday revealed that two competing threads could indeed try and insert twice despite checking for an existing record and caused a Unique Key error 2601. As this was all being coded in a stored procedure we looked to see what error handling T-SQL provided us.
Whilst you can detect the error number after a T-SQL statement in a stored procedure by querying the global variable @@ERROR, you cannot prevent SQL Server 2000 from sending an exception error to the calling script. Therefore the following snippet does work, but you will still get an exception thrown. This is not the complete script but highlights the additional features which we used to get this working:
-- stored procedure snippet -- declare a local variable to hold the error number -- The global variable @@error gets reset after each -- command so you would lose the error number following -- the next statement should you wish to do extra things with it -- like return it back to the script calling it DECLARE @err int ... -- update the parts received UPDATE [PendingMultipartMessage] ... -- check to see if the update ran by checking -- the SET value of @multipartMessageID from the ID field IF (@multipartMessageID is NULL) BEGIN SET @multipartMessageID = NEWID() INSERT INTO [PendingMultipartMessage] (...) VALUES (...) SELECT @err = @@error IF (@err = 2601) BEGIN -- try updating instead of inserting as the record -- exists as the 2601 error says there's already a unique key UPDATE [PendingMultipartMessage] ... END END
All that’s left to do is to wrap your stored procedure call in a try…catch wrapper and catch any SQLExceptions and check they’re not 2601. You can do what you like with the error, but choosing to ignore it means your application will no longer fail.
Improvements in SQL Server 2005 have afforded developers a TRY CATCH definition in T-SQL to help catch errors within stored procedures. This feels a lot more sensible as the database is the singular resource we’re trying to gain shared access to and it should be able to handle these errors internally without an extra callback. Just to remind you, the mainstream support for SQL Server 2000 ends in April 2008.