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)?