Re: concurrency problem with lists ("check constraint" on groups of rows)

B D Jensen (bjorn.d.jensen@xxxxxxxxx) writes:
I now tried with applock, but:
1. session completes
other session are blocked (after 10minutes i stopped all)

Whats wrong in code below?

I tested it, and I did not see the problem you describe, but it worked as
expected. I had to clean up your procedure bit, because I use a
case-sensitive collation. I don't think that is the issue though. My guess
is that you made the classical mistake of pressing the red button to
cancel the batch while it was running, and then forgot to rollback the
open transaction.

For the record, here my version that also works with a case-sensitive
collation:

CREATE PROCEDURE createMyGroup
@list_in VARCHAR(MAX) -- #param list of id's
, @myGroupID_out INT OUTPUT -- #param pseudo key
AS /*
Description: variation of sql divide
Change History: $Date: $ , $Author: bdj $, $Revision: 0 $
*/
BEGIN
SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @rowcnt INT ;
DECLARE @list TABLE (id INT PRIMARY KEY) ; -- important constraint!
DECLARE @message VARCHAR(100)
DECLARE @groupid INT
DECLARE @res INT

SELECT @myGroupID_out = NULL ; -- real output
-- exec utility.debug_info '.....'

BEGIN TRY
BEGIN TRANSACTION

EXEC @res = sp_getapplock @Resource = 'myGroup', @LockMode =
'Update', @LockOwner = 'Transaction', @LockTimeout = 5000 -- 5000ms


-- SELECT @groupID = myGroupID
-- FROM myGroup WITH (UPDLOCK) -- select only for lock purpose


INSERT INTO @list
SELECT CONVERT(INT, s.data)
FROM dbo.split(@list_in, ',') s


SELECT @myGroupID_out = a2.myGroupID
FROM dbo.myGroupMember a2
WHERE EXISTS ( SELECT NULL
FROM @list b1
WHERE b1.id = a2.ID )
GROUP BY a2.myGroupID
HAVING (SELECT COUNT (*) FROM myGroupMember a3 WHERE a3 .
myGroupID = a2 . myGroupID) = (SELECT COUNT (*) FROM @list) AND
COUNT(*) = (SELECT COUNT (*) FROM @list)


SELECT @rowcnt = @@ROWCOUNT
-- WAITFOR DELAY '00:01:01' -- wait 1min 1 second
IF @rowcnt > 1 -- when this happens we have a problem
BEGIN


SELECT @message = 'More than 1 myGroupID ' + COALESCE(CAST(@myGroupID_out AS VARCHAR(100)), 'null ')
SELECT @message = @message + COALESCE(CAST(id AS VARCHAR(100)), '-') + ','
FROM @list
SELECT @myGroupID_out = NULL


RAISERROR (@message , 16 , 1)
END
ELSE
IF @rowcnt = 1
BEGIN
PRINT 'be happy'
END
ELSE
IF @rowcnt = 0
BEGIN
IF @res NOT IN (0,1)
BEGIN
RAISERROR('Unable to acquire lock', 16, 1)
END
ELSE
BEGIN
INSERT INTO dbo.myGroup
DEFAULT VALUES ;
SELECT @myGroupID_out = SCOPE_IDENTITY() ;


INSERT INTO dbo.myGroupMember
SELECT @myGroupID_out gid, id
FROM @list ;
END
END ;
EXEC @res = sp_releaseapplock @Resource = 'myGroup', @LockOwner = 'Transaction'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT @myGroupID_out = NULL
--EXEC raise_error
SELECT ERROR_MESSAGE()
END CATCH
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END ;
GO


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.