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

Hi!
In the meantime I found out that most of the actual problems came from
another corner: an FK with "on cascade delete", which result in
dublet groups. But the problem is still actual since I plan to scale-
up the application; but before doing that I must ensure that it will
be able
to handle more concurrency.

Erland presented 2 good solutions here:
1. updlock
2. application lock

At another place he also suggested use of Service Broker - and I
expect that use of SB will be better than my DYI of queues (even if I
think my do-yourself-implementation is simpler).

The reason why got these "unable to acquire lock" is that I had not
attention on the fact that my sleep step took 61s, but the application
lock was set to 50s - so of course I must get this error. The sleep
step was to ensure simulate some form of "stress" on system.

Here the final code that now works as I expect, and thanks to people
for their help (-:

DROP TABLE myGroup ;


CREATE TABLE myGroup (
[myGroupID] [int] IDENTITY(1, 1) NOT NULL
, CONSTRAINT [PK_myGroup] PRIMARY KEY CLUSTERED ([myGroupID] ASC)
) ;


DROP TABLE myGroupMember ;


CREATE TABLE myGroupMember (
[myGroupMemberID] [int] IDENTITY(1, 1) NOT NULL
, [myGroupID] [int] NOT NULL
, [ID] [int] NOT NULL
, CONSTRAINT [PK_myGroupMember] PRIMARY KEY CLUSTERED
([myGroupMemberID] ASC)
, CONSTRAINT [UK_myGroupMember_myGroupID_ID] UNIQUE NONCLUSTERED
([myGroupID] ASC, [ID] ASC) -- not enough because this will not avoid
dublet lists
) ;
-- FK connecting myGroupMember.myGroupID with myGroup.myGroupID...
CREATE UNIQUE INDEX UK_myGroupMember_ID_myGroupID ON myGroupMember
(ID, myGroupID);


DROP PROC createMyGroup
GO




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 =100000 -- 100000ms
= 100s


-- 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 = 61 s
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


DECLARE @list VARCHAR(MAX), @myGroupID INT
EXEC createMyGroup '1000,2000,3000,4000', @myGroupID OUTPUT
SELECT @myGroupID


-- from more than 1 session simultaneous:
WAITFOR TIME '14:33';
DECLARE @list VARCHAR(MAX), @myGroupID INT
EXEC createMyGroup '1000,2000,3000,4000,5008', @myGroupID OUTPUT
SELECT @myGroupID


Best regards
Bjorn D. Jensen
P. S. Feel free to use this code example for simulating concurrency
problems ;-)