CODEWITHSUNDEEP
sqlsql-servermultithreadingt-sqllocking
WilfriedVS
WilfriedVS

Reputation: 416

SQLserver multithreaded locking with TABLOCKX

I have a table "tbluser" with 2 fields:

I have a multithreaded/multi server application that uses this table.

I want to accomplish the following:

I have the following stored procedure:

CREATE PROCEDURE uniqueuser @user nvarchar(100) AS
BEGIN
BEGIN TRAN
    DECLARE @userID int
    SET  nocount ON
    SET  @userID = (SELECT @userID FROM tbluser WITH (TABLOCKX) WHERE  [user] = @user)
    IF @userID <> ''
    BEGIN
        SELECT  userID = @userID
    END
    ELSE
    BEGIN
        INSERT INTO  tbluser([user]) VALUES (@user)
        SELECT userID = SCOPE_IDENTITY() 
    END 
COMMIT TRAN
END

Basically the application calls the stored procedure and provides a username as parameter. The stored procedure either gets the userid or insert the user if it is a new user.

Am I correct to assume that the table is locked (only one server can insert/query)?

Upvotes: 1

Views: 1764

Answers (2)

Jim
Jim

Reputation: 41

I'm sure the following advice might help someone in the future.

Instead of (TABLOCKX), try (TABLOCKX, HOLDLOCK) or even better, if this is the only procedure in which writing to tbluser takes place, you can cut down on a whole bunch of code entirely using only TABLOCKX and no transaction via

CREATE PROCEDURE uniqueuser @user nvarchar(100)
AS
--BEGIN TRAN NOT NECESSARY!
    INSERT tbluser WITH (TABLOCKX) ([user])
        SELECT @user
        WHERE NOT EXISTS(SELECT 1 FROM tbluser WHERE [user]=@user)
--COMMIT TRAN NOT NECESSARY!
SELECT userID FROM tbluser WHERE [user]=@user

This way, the insert statement (which automatically creates a transaction for the duration of the INSERT) is the only time you need the table lock. (Yes, I stress-tested this on two windows both with and without TABLOCKX to see how it faired before posting my message.)

Upvotes: 4

gbn
gbn

Reputation: 432230

If you want to guarantee that the user is unique, the only way is to a unique constraint

ALTER  TABLE tbluser WITH CHECK
   ADD CONSTRAINT UQ_tbluser_user UQNIUE (user);

Do not "roll your own" unique checks: it will fail.

The cached data in the server's memory conforms to the same constraint

I'd do this. Look for user first, if not found insert, handle unique error just in case. And I'd use an OUTPUT parameter

CREATE PROCEDURE uniqueuser
  @user nvarchar(100)
  -- ,@userid int = NULL OUTPUT
AS
SET NOCOUNT, XACT_ABORT ON;
DECLARE @userID int;

BEGIN TRY
    SELECT @userID FROM tbluser WHERE [user] = @user;
    IF @userID IS NULL
    BEGIN
        INSERT INTO  tbluser([user]) VALUES (@user);
        SELECT userID = SCOPE_IDENTITY() ;
    END
END TRY
BEGIN CATCH
    -- test for a concurrent call that just inserted before we did
    IF ERROR_NUMBER() = 2627
        SELECT @userID FROM tbluser WHERE [user] = @user;
    ELSE
       -- do some error handling
END CATCH
-- I prefer output parameter for this SELECT @userID AS UserID
GO

Edit: why TABLOCKX fails...

  • You only lock the table for the duration of the SELECT.
  • A 2nd process running concurrently will starting reading the table after the lock is released by process 1
  • Both processes can have @userID IS NULL because process 1 has not yet executed the INSERT
  • Process 2 gets an error when it INSERTS

This happens because TABLOCKX modifies lock isolation and granularity, not duration.

Edit 2: for SQL Server 2000

CREATE PROCEDURE uniqueuser
   @user nvarchar(100)
   -- ,@userid int = NULL OUTPUT
AS
SET NOCOUNT, XACT_ABORT ON;
DECLARE @userID int;

SELECT @userID FROM tbluser WHERE [user] = @user;
IF @userID IS NULL
BEGIN
    INSERT INTO  tbluser([user]) VALUES (@user);
    IF @@ERROR = 2627
        SELECT @userID FROM tbluser WHERE [user] = @user;
    ELSE
        RAISERROR ('the fan needs cleaning', 16, 1);   
    SELECT userID = SCOPE_IDENTITY();
END
GO

Upvotes: 2

Related Questions