Blocking query and improper transaction declaration

I inherited the following ... which, when I captured the blocking chains ... it is at the top of it.
Question;
Is the TABLOCKX necessary since the default is READCOMMITED?
dOES THE SCOPE OF THE TRANSACTION HAS TO BE THIS WIDE?
CAN I GET RID OF IT?

Thank you,
*************


CREATE PROCEDURE usp_create_mlsnum_for_slimm
   @strNumOut VARCHAR (20) OUTPUT
AS
   DECLARE
      @value     INT,
      @str_new   VARCHAR (12)
   BEGIN TRAN

   SELECT    TOP 1
             @value = intmlsnum
        FROM mls_numbers_for_slimm WITH (TABLOCKX)
       WHERE blnused = 0
    ORDER BY intmlsnum
   --print 'value is '+cast(@value as varchar(7))

   SET @str_new =
          right (year (getdate ()), 2) + '-' + cast (@value AS VARCHAR (6))

   --print 'mlsnum is '+@str_new

   UPDATE mls_numbers_for_slimm
      SET blnused = 1, action_date = getdate (), strmlsnum = @STR_NEW
    WHERE intmlsnum = @value

   SELECT @strNumOut = @STR_NEW

   COMMIT TRAN
DBA2000Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
There are at least 2 or 3 approaches that I can think of; this is one of them:

CREATE PROCEDURE usp_create_mlsnum_for_slimm
			@strNumOut VARCHAR(20) OUTPUT
AS 

SET NOCOUNT ON

UPDATE  m
SET     blnused = 1,
        action_date = GETDATE(),
        strmlsnum = RIGHT(YEAR(GETDATE()), 2) + '-' + CAST(d.intmlsnum AS VARCHAR(6)),
        @strNumOut = RIGHT(YEAR(GETDATE()), 2) + '-' + CAST(d.intmlsnum AS VARCHAR(6))
FROM	mls_numbers_for_slimm m
	INNER JOIN (
		SELECT	TOP 1
			intmlsnum
		FROM    mls_numbers_for_slimm
		WHERE   blnused = 0
		ORDER BY 
			intmlsnum) d ON m.intmlsnum = d.intmlsnum

Open in new window

If intmlsnum is unique it is possible that you can simplify that even further.

Whatever solution you reach,  make sure there is an index on intmlsnum.
0
 
ChetOS82Commented:
Once you read the table you don't want another transaction to read the table and get the same value you did, because both of you would pull the same value and "blnused" on it.

TABLOCKX prevents another transaction from reading the table while your transaction runs.  It has to be this way.

And you need READ COMMITTED otherwise you would see changes made by other transactions that haven't been committed yet.
0
 
Anthony PerkinsCommented:
Actually the Transaction is not needed, as you can write that in a single query and avoid all the problems in the first place.  Let me know if you are interested.
0
 
DBA2000Author Commented:
Hi, acperkins

I am very much interested ... will u pls show how.

Thanks in advance.
0
 
DBA2000Author Commented:
Thank u!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.