[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Blocking query and improper transaction declaration

Posted on 2012-08-31
5
Medium Priority
?
454 Views
Last Modified: 2012-09-24
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
0
Comment
Question by:DBA2000
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:ChetOS82
ID: 38355398
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38356396
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
 

Author Comment

by:DBA2000
ID: 38358368
Hi, acperkins

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

Thanks in advance.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38359129
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
 

Author Closing Comment

by:DBA2000
ID: 38430382
Thank u!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question