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
[at] list_in VARCHAR(MAX) -- $param list of id's
, [at] 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 [at] rowcnt INT ;
DECLARE [at] list TABLE (id INT PRIMARY KEY) ; -- important constraint!
DECLARE [at] message VARCHAR(100)
DECLARE [at] groupid INT
DECLARE [at] res INT
SELECT [at] myGroupID_out = NULL ; -- real output
-- exec utility.debug_info '.....'
BEGIN TRY
BEGIN TRANSACTION
EXEC [at] res = sp_getapplock [at] Resource = 'myGroup', [at] LockMode =
'Update', [at] LockOwner = 'Transaction', [at] LockTimeout =100000 -- 100000ms
= 100s
-- SELECT [at] groupID = myGroupID
-- FROM myGroup WITH (UPDLOCK) -- select only for lock purpose
INSERT INTO [at] list
SELECT CONVERT(INT, s.data)
FROM dbo.split( [at] list_in, ',') s
SELECT [at] myGroupID_out = a2.myGroupID
FROM dbo.myGroupMember a2
WHERE EXISTS ( SELECT NULL
FROM [at] 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 [at] list) AND
COUNT(*) = (SELECT COUNT (*) FROM [at] list)
SELECT [at] rowcnt = [at] [at] ROWCOUNT
WAITFOR DELAY '00:01:01' -- wait 1min 1 second = 61 s
IF [at] rowcnt > 1 -- when this happens we have a problem
BEGIN
SELECT [at] message = 'More than 1 myGroupID ' +
COALESCE(CAST( [at] myGroupID_out AS VARCHAR(100)), 'null ')
SELECT [at] message = [at] message + COALESCE(CAST(id AS
VARCHAR(100)), '-') + ','
FROM [at] list
SELECT [at] myGroupID_out = NULL
RAISERROR ( [at] message , 16 , 1)
END
ELSE
IF [at] rowcnt = 1
BEGIN
PRINT 'be happy'
END
ELSE
IF [at] rowcnt = 0
BEGIN
IF [at] res NOT IN (0,1)
BEGIN
RAISERROR('Unable to acquire lock', 16, 1)
END
ELSE
BEGIN
INSERT INTO dbo.myGroup
DEFAULT VALUES ;
SELECT [at] myGroupID_out =
SCOPE_IDENTITY() ;
INSERT INTO dbo.myGroupMember
SELECT [at] myGroupID_out
gid, id
FROM [at] list ;
END
END ;
EXEC [at] res = sp_releaseapplock [at] Resource = 'myGroup', [at] LockOwner =
'Transaction'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT [at] myGroupID_out = NULL
--EXEC raise_error
SELECT ERROR_MESSAGE()
END CATCH
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END ;
GO
DECLARE [at] list VARCHAR(MAX), [at] myGroupID INT
EXEC createMyGroup '1000,2000,3000,4000', [at] myGroupID OUTPUT
SELECT [at] myGroupID
-- from more than 1 session simultaneous:
WAITFOR TIME '14:33';
DECLARE [at] list VARCHAR(MAX), [at] myGroupID INT
EXEC createMyGroup '1000,2000,3000,4000,5008', [at] myGroupID OUTPUT
SELECT [at] myGroupID
Best regards
Bjorn D. Jensen
P. S. Feel free to use this code example for simulating concurrency
problems ;-)
