SQLserver multithreaded locking with TABLOCKX
- by WilfriedVS
I have a table "tbluser" with 2 fields:
userid = integer (autoincrement)
user = nvarchar(100)
I have a multithreaded/multi server application that uses this table.
I want to accomplish the following:
Guarantee that field user is unique in my table
Guarantee that combination userid/user is unique in each server's memory
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)?
