5th Feb 2010

Lynn,

We have a mission critical app which has this problem in the very guts of the business logic and is causing deadlocks & major disruption on a 50 user installation. I am not a DBA but it appears that your tuning suggestion removes a read and thus halves the disk activity. I would also like to understand better the implications and effect of the two statements regarding transaction isolation levels and how the changes proposed below might affect our application.

I believe we could make an immediate improvement by just modifying our stored procedure as indicated by the lines below marked **:

ALTER PROCEDURE [dbo].[sp_nxnumber]

@Result Int output,

@NumberType Varchar(20)

AS

**SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

UPDATE nxno WITH (tablockx) SET NxNumber = NxNumber + 1 WHERE numberType = @NumberType

IF @@error <> 0

BEGIN

ROLLBACK TRANSACTION

RAISERROR ('NxNumber Update Error', 16, 2) WITH NOWAIT, SETERROR

RETURN 0

END

** REMOVE NEXT LINE

-- SET @Result = (Select NxNumber FROM NxNo WITH (tablockx) WHERE NumberType = @NumberType)

COMMIT TRANSACTION

**SET TRANSACTION ISOLATION LEVEL READ COMMITTED

**SET @Result = NxNumber

ANY OTHER COMMENTS WELCOME

Peter HORSLEY

Melbourne, Australia