Use of TABLOCKX

21 Jan 2006 3:16 AM

PD

I have a stored proc that generates keys based on an existing table
row. Here's the code:
=====
CREATE PROCEDURE [GetNewID]
@newID char(4) OUTPUT
AS

DECLARE @lastID char(4), @userIDNum int

-- Get the latest existing userid from Users and put it in the
Reference table
IF NOT EXISTS(SELECT RefID FROM Reference WHERE ReferenceType =
'LastUserID')
BEGIN
IF EXISTS(SELECT userid FROM Users WHERE userid LIKE '[A-Z]%')
INSERT INTO Reference WITH(TABLOCKX) (ReferenceType, ReferenceCode)
SELECT 'LastUserID', MAX(Usersid) AS ID FROM Users WHERE
ISNUMERIC(userid) = 0
ELSE
INSERT INTO Reference WITH(TABLOCKX) (ReferenceType, ReferenceCode)
SELECT 'LastUserID', CAST(MAX(CAST(Usersid AS int)) AS char(4)) AS ID
FROM Users WHERE ISNUMERIC(userid) = 1
END
ELSE
BEGIN
IF EXISTS(SELECT userid FROM Users WHERE userid LIKE '[A-Z]%')
UPDATE Reference WITH(TABLOCKX) SET ReferenceCode = (SELECT
MAX(Usersid) AS ID FROM Users WHERE ISNUMERIC(userid) = 0) WHERE
ReferenceType = 'LastUserID'
ELSE
UPDATE Reference WITH(TABLOCKX) SET ReferenceCode = (SELECT
CAST(MAX(CAST(Usersid AS int)) AS char(4)) AS ID FROM Users WHERE
ISNUMERIC(userid) = 1) WHERE ReferenceType = 'LastUserID'
END

-- Generate next userid
SELECT @lastID = ReferenceCode FROM Reference WHERE ReferenceType =
'LastUserID'
IF ISNUMERIC(@lastID) = 1
BEGIN
SELECT @userIDNum = CAST(@lastID AS int) + 1
SELECT @newID = CASE WHEN @userIDNum < 10000 THEN CAST(@userIDNum AS
char(4)) --When userid values < 9999, increment as int
WHEN @userIDNum = 10000 THEN 'A' + RIGHT(@userIDNum , 3) END FROM
Reference WITH(TABLOCKX)--Generate the first alphanumeric user id
END
ELSE -- get next alphanumeric user id
BEGIN
SELECT @userIDNum = CAST(RIGHT(@lastID, 3) AS int) + 1
SELECT @newID = CASE WHEN @userIDNum < 1000 THEN LEFT(@lastID, 1) +
RIGHT('00' + CAST(@userIDNum AS varchar), 3) --When numeric portion <
999, increment and retain alphabetic portion
WHEN @userIDNum = 1000 THEN CHAR(ASCII(LEFT(@lastID, 1)) + 1) +
RIGHT(@userIDNum , 3) END FROM Reference WITH(TABLOCKX)--Generate next
letter for alphabetic portion
END
=====
My question is, if two connections A and B invoke this stored
procedure, will the invocation for A finish execution before B? Or will
B get a chance to insert/update the value of 'LastUserID' before A
generates a new value?

21 Jan 2006 3:25 PM

Dan Guzman

> My question is, if two connections A and B invoke this stored
> procedure, will the invocation for A finish execution before B? Or will
> B get a chance to insert/update the value of 'LastUserID' before A
> generates a new value?

It is possible to get undesired results unless this proc is executed in the
context of a transaction. However, I'm not I'm not fully sure I understand
what you are trying to accomplish. The Users table is used as the key
source here but that table is not modified by this proc. It is therefore
possible for different connections to get the same @newID value unless the
execution of this proc and the Users table INSERT are all executed in the
context of the same transaction.

Why use the Users table at all? It seems to me you could use only the
reference table as the authoritative source of the next value. Also, it
appears that using the alpha character in the first position of the key was
a kludge implemented when over 9999 values were generated and will again
have problems after 36000 values. I hope this kludge is just an interim fix
for a legacy application until the core issue is resolved.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"PD" <mrfuzz***@yahoo.com> wrote in message
news:1137813362.028685.181730@g44g2000cwa.googlegroups.com...
>I have a stored proc that generates keys based on an existing table
> row. Here's the code:
> =====
> CREATE PROCEDURE [GetNewID]
> @newID char(4) OUTPUT
> AS
>
> DECLARE @lastID char(4), @userIDNum int
>
> -- Get the latest existing userid from Users and put it in the
> Reference table
> IF NOT EXISTS(SELECT RefID FROM Reference WHERE ReferenceType =
> 'LastUserID')
> BEGIN
> IF EXISTS(SELECT userid FROM Users WHERE userid LIKE '[A-Z]%')
> INSERT INTO Reference WITH(TABLOCKX) (ReferenceType, ReferenceCode)
> SELECT 'LastUserID', MAX(Usersid) AS ID FROM Users WHERE
> ISNUMERIC(userid) = 0
> ELSE
> INSERT INTO Reference WITH(TABLOCKX) (ReferenceType, ReferenceCode)
> SELECT 'LastUserID', CAST(MAX(CAST(Usersid AS int)) AS char(4)) AS ID
> FROM Users WHERE ISNUMERIC(userid) = 1
> END
> ELSE
> BEGIN
> IF EXISTS(SELECT userid FROM Users WHERE userid LIKE '[A-Z]%')
> UPDATE Reference WITH(TABLOCKX) SET ReferenceCode = (SELECT
> MAX(Usersid) AS ID FROM Users WHERE ISNUMERIC(userid) = 0) WHERE
> ReferenceType = 'LastUserID'
> ELSE
> UPDATE Reference WITH(TABLOCKX) SET ReferenceCode = (SELECT
> CAST(MAX(CAST(Usersid AS int)) AS char(4)) AS ID FROM Users WHERE
> ISNUMERIC(userid) = 1) WHERE ReferenceType = 'LastUserID'
> END
>
> -- Generate next userid
> SELECT @lastID = ReferenceCode FROM Reference WHERE ReferenceType =
> 'LastUserID'
> IF ISNUMERIC(@lastID) = 1
> BEGIN
> SELECT @userIDNum = CAST(@lastID AS int) + 1
> SELECT @newID = CASE WHEN @userIDNum < 10000 THEN CAST(@userIDNum AS
> char(4)) --When userid values < 9999, increment as int
> WHEN @userIDNum = 10000 THEN 'A' + RIGHT(@userIDNum , 3) END FROM
> Reference WITH(TABLOCKX)--Generate the first alphanumeric user id
> END
> ELSE -- get next alphanumeric user id
> BEGIN
> SELECT @userIDNum = CAST(RIGHT(@lastID, 3) AS int) + 1
> SELECT @newID = CASE WHEN @userIDNum < 1000 THEN LEFT(@lastID, 1) +
> RIGHT('00' + CAST(@userIDNum AS varchar), 3) --When numeric portion <
> 999, increment and retain alphabetic portion
> WHEN @userIDNum = 1000 THEN CHAR(ASCII(LEFT(@lastID, 1)) + 1) +
> RIGHT(@userIDNum , 3) END FROM Reference WITH(TABLOCKX)--Generate next
> letter for alphabetic portion
> END
> =====
> My question is, if two connections A and B invoke this stored
> procedure, will the invocation for A finish execution before B? Or will
> B get a chance to insert/update the value of 'LastUserID' before A
> generates a new value?
>

21 Jan 2006 6:12 PM

Alexander Kuznetsov

I'm not sure what exactly you are trying to accomplish, but I would
first try something really simple like this

create table Reference(RefId int, RefName varchar2(30), toggle int)
insert into Refernce values(0, "MyTable', 0)
go

(snip)
begin tran
---- first of all, acquire an update lock
update Reference set toggle=1-toggle where Refname=@name
--- do the rest of your logic here

25 Jan 2006 3:01 PM

PD

Thanks for your responses. Unfortunately, this is indeed a temporary
fix to a legacy system so I can't do the Reference table over. If I
sandwich the code between a BEGIN TRANS and COMMIT TRANS, will it
guarantee that one invocation of the proc will return before another
invocation can start?

26 Jan 2006 1:05 AM

Dan Guzman

The TABLOCKX hint on the Reference table insert/update will prevent another
user from accessing that table until the transaction is committed or rolled
back.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"PD" <mrfuzz***@yahoo.com> wrote in message
news:1138197912.167862.299990@z14g2000cwz.googlegroups.com...
> Thanks for your responses. Unfortunately, this is indeed a temporary
> fix to a legacy system so I can't do the Reference table over. If I
> sandwich the code between a BEGIN TRANS and COMMIT TRANS, will it
> guarantee that one invocation of the proc will return before another
> invocation can start?
>