Business Scenario: This is a ticketing system, and we got so many user using the application. When a ticket(stored in 1st table in below) comes in to the application, any user can hit the ownership button and take ownershipf of it. Only one user can take ownership for one ticket. If two user tries to hit the ownership button, first one wins and second gets another incident or message that no incident exists to take ownership. Here i am facing a concurrency issue now. I already have a lock implementation using another table(2nd table in below).
I have two tables;
Table(Columns)
- Ticket(TicketID-PK, OwnerUserID-FK)
- TicketOwnerShipLock(TicketID-PK, OwnerUserID-FK, LockDate)Note: Here TicketID is set as Primary Key.
Current lock implementation: whenever user one tries to own ticket puts an entry to 2nd table with TicketID, UserID and current date,then goes to update the OwnerUserID in 1st table. Before insert the above said lock entry, Procedure checks for any other user already created any lock for the same incident. If already there is lock, lock wont be opened for the user. Else lock entry wont be entered and the user cannot update the ticket onwership. More Info: There are so many tickets getting opened in 1st table, whenever user tries to take ownership, we should find the next available ticket to take ownership. So need to find ticket and to do some calculation and set a status for that ticket, there one more column in 1st table StatusID. Status will be assigned as Assigned.
Problem: Somehow two user's got the ownership for same ticket at excatly same time, i have even checked the millisecond but that too same. 1. I would like to know if any MS SQL Server LOCK would help in this scenario. 2. Or do i need to block table while insert.(This 2nd rable will not have much data approx. less than 15 rows)
Lock Creation Procedure Below:
ALTER PROCEDURE [dbo].[TakeOwnerShipGetLock]
@TicketId [uniqueidentifier],
@OwnerId [uniqueidentifier]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION TakeOwnership
BEGIN TRY
DECLARE @Lock BIT
SET @Lock = 0
DECLARE @LockDate DATETIME
SELECT @LockDate = LockDate
FROM dbo.TakeOwnershipLock
WHERE TicketId = @TicketId
IF @LockDate IS NULL
AND NOT EXISTS ( SELECT 1
FROM dbo.TakeOwnershipLock as takeOwnership WITH (UPDLOCK)
INNER JOIN dbo.Ticket as Ticket WITH (NOLOCK)
ON Ticket.TicketID = takeOwnership.TicketId
WHERE takeOwnership.TicketId = @TicketId
AND Ticket.OwnerID is NULL )
BEGIN
INSERT INTO dbo.TakeOwnershipLock
( TicketId
,OwnerId
,LockDate
)
VALUES ( @TicketId
,@OwnerId
,GETDATE()
)
IF ( @@ROWCOUNT > 0 )
SET @Lock = 1
END
SELECT @Lock
COMMIT TRANSACTION TakeOwnership
END TRY
BEGIN CATCH
-- Test whether the transaction is uncommittable.
IF XACT_STATE() = 1
BEGIN
COMMIT TRANSACTION TakeOwnership
SET @Lock = 1
SELECT @Lock
END
-- Test whether the transaction is active and valid.
IF XACT_STATE() = -1
BEGIN
ROLLBACK TRANSACTION TakeOwnership
SET @Lock = 0
SELECT @Lock
END
END CATCH
END
