none
SQL table hints not working (UPDLOCK, HOLDLOCK) RRS feed

  • Question

  • I have this SP called from C# application in multiple machines and instances at once.

    There's an UploadStarted field that is checked first if zero, updated to 1 and returns if row updated or already 1

    I tried different table hints in both SELECT and UPDATE but it doesn't work and two or more process get the same record.

    CREATE PROCEDURE [dbo].[CheckUploadStatus] @QueueID int, @RowsUpdated int OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRAN UPDATE CasesQueue WITH (SERIALIZABLE, UPDLOCK) SET UploadStarted=1, UploadStartedAt=getdate() where id IN (SELECT id FROM CasesQueue WITH (updlock, holdlock) where id=@QueueID and UploadStarted=0) SELECT @RowsUpdated=@@ROWCOUNT COMMIT TRAN END

    Can I replace query with following?

    UPDATE CasesQueue WITH (SERIALIZABLE, UPDLOCK) SET UploadStarted=1, UploadStartedAt=getdate() where id =@QueueID and UploadStarted=0


    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.

    Thursday, September 12, 2013 9:10 AM

Answers

All replies

  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    You have only one DML operation why do you need BEGIN TRAN... It is atomic operation....

    http://technet.microsoft.com/en-us/library/ms173763.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by Microsoftee Monday, September 16, 2013 9:12 AM
    Thursday, September 12, 2013 9:48 AM
    Answerer
  • Yes, it's atomic but it's not working well even without BEGIN TRAN

    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.

    Thursday, September 12, 2013 10:44 AM
  • "not working well" is a description that does not provide any information on which to offer suggestions.  In addition, you should generally avoid query hints unless you are ABSOLUTELY certain they are needed and you understand their effects.  As Uri suggested,  you don't need the hints nor a transaction nor do you need to supply hints for a table that is the target of the update.  In addition, there is a much simpler form of the update statement you could use - so why use this more complicated form in the first place?  Let's pick this statement apart:

    UPDATE CasesQueue WITH SET <don't care>
    where id IN (SELECT id FROM CasesQueue WITH (updlock, holdlock) where id=@QueueID and UploadStarted=0)

    Here your subquery should (presumably, based on the column name) select a single row at most.  In effect, you simply want to update the row in CasesQueue where id = @QueueID if UploadStarted = 0.  So why do you need to use a subquery?  Why not just use:

    UPDATE CasesQueue WITH SET UploadStarted=1, UploadStartedAt=getdate()
    where id id=@QueueID and UploadStarted=0;

    Notice that I have terminated this statement with a semicolon - a habit you should develop.  What is the purpose of HoldLock?  If we look at the documentation, we see that it is equivalent to serializable and holds any locks for the duration of a transaction - which is effectively this single statement.  Therefore, it does nothing useful.  You also have UPDLOCK.  Well, your statement is updating the row in the first place, so an update lock will be acquired regardless of the hint.  Again, there is no need for this hint.  In short, you seem to be throwing hints at a process without any clear reasoning.  If you don't really understand what the source of the problem is, adding more hints to your query is unlikely to solve it.   

    One last comment.  Your procedure name is CheckUploadStatus - but it does not seem to me that your procedure is CHECKING anything.  I'm guessing that this is the first step in starting an upload process.  Confusion leads to mistakes and errors.

    Thursday, September 12, 2013 12:59 PM
  • I am not sure I understand the problem. You currently have only one statement, wrapped in an explicit transaction. That statement will run and do exactly what it is told to do: update all rows for the @QueueID where UploadStarted is still 0.

    The two statements are equivalent, and in this case, the query hints in both statements don't add any value.

    It is not clear what your problem is. Most likely, you are having problems when you try to use the result of this query. What I am trying to say, is that if "id" is declared as unique (with a Unique constraint, Primary Key constraint or Unique index), then for two simultaneous connections, only one connection will return "1".


    Gert-Jan

    Thursday, September 12, 2013 5:34 PM
  • First of all thanks for your time.

    The problem I'm facing here is, I have multiple instances of desktop application running, uploading records simultaneously, the problem is more than two instances going for same record to upload.

    My application is loading all the pending records (UploadStarted=0) in DataTable and then before starting upload it calls this SP to check if it's already started to upload, if not SP sets UploadStarted to 1 and returns 1 and application starts uploading record. The ID field is primary key so, it'll always update single record at a time.

    Initially I had query without sub query and without TRAN but it was not working and both instances running for same record to upload then I changed it to sub query form but then I came to know problem remained there.

    The name of SP CheckUploadStatus I kept because it's checking status and changing it to start upload.

    I even tried

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    Here is my SP now but it's still not locking and both apps getting same record

    CREATE PROCEDURE [dbo].[CheckUploadStatus] @QueueID int, @RowsUpdated int OUTPUT AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE UPDATE CasesQueue WITH (SERIALIZABLE, UPDLOCK) SET UploadStarted=1, UploadStartedAt=getdate() where id =@QueueID and UploadStarted=0 SELECT @RowsUpdated=@@ROWCOUNT
    END

    Can someone guide me about related table hints and how can I use it to resolve my issue.

    Thanks in advance for your support.


    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.


    • Edited by Microsoftee Sunday, September 15, 2013 8:55 AM Added additional code
    Sunday, September 15, 2013 8:05 AM
  • >>>Here is my SP now but it's still not locking and both apps getting same record

    What are they getting ?@RowsUpdated?id =@QueueID? Is it ID unique?

    See Tony's blog

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 9:03 AM
    Answerer
  • Thanks for quick response.

    The ID field is auto generated primary key and unique.

    SP is returning the number of rows updated using Output parameter by following

    SELECT @RowsUpdated=@@ROWCOUNT


    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.

    Sunday, September 15, 2013 9:27 AM
  • If ID column is UNIQUE, how do you get the same value? Or do  you get a error?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 9:34 AM
    Answerer
  • If you check the single line code of my SP, it's just an update query and then by @@ROWCOUNT I'm getting if row has updated or not.

    I have suspicion that @@ROWCOUNT may not giving me right result for same query.

    Here's my whole SP code again for your reference, I'm passing ID value and getting back if row updated or not.

    CREATE PROCEDURE [dbo].[CheckUploadStatus] 
    	@QueueID int, 
    	@RowsUpdated int OUTPUT
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    	UPDATE CasesQueue WITH (SERIALIZABLE, UPDLOCK) SET UploadStarted=1, UploadStartedAt=getdate() where id =@QueueID and UploadStarted=0
    	SELECT @RowsUpdated=@@ROWCOUNT
    END


    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.

    Sunday, September 15, 2013 9:49 AM
  • You have suspicions or you can proof it ?

    I have Alex's' book described situations like yours

    http://www.red-gate.com/community/books/defensive-database-programming


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 9:57 AM
    Answerer
  • No, I don't have proof, I just think about it that if everything else look OK then this may have issue.

    Have you got the whole idea of my problem, it's not too complex to understand.


    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.


    • Edited by Microsoftee Sunday, September 15, 2013 11:16 AM Grammar
    Sunday, September 15, 2013 11:12 AM
  • As I said before, the problem is probably not in the code snippet you posted.

    How are you calling the stored procedure? Are you including the OUTPUT keyword in the code that calls the SP? Because without it, the OUTPUT mechanism will not work.

    Make sure that if you pass in an output parameter, that it is loaded with a value that is never returned from the stored procedure. In your case, a value like -1 seems appropriate.

    In general: how do you currently establish that something is wrong, and why do you think the problem is in the stored procedure?


    Gert-Jan

    Sunday, September 15, 2013 11:20 AM
  • Hi 

    Gert-Jan just asked exactly what I was about to ask you.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 11:27 AM
    Answerer
  • Gert,

    Here is few lines of code in application.

                SqlParameter paramter = new SqlParameter();
                paramter.SqlDbType = SqlDbType.Int;
                sqlCmd.Parameters.AddWithValue("QueueID", 0);
                paramter = new SqlParameter();
                paramter.ParameterName = "RowsUpdated";
                paramter.SqlDbType = SqlDbType.Int;
                paramter.Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add(paramter);
                foreach (CasesQueue aQueuedCase in QueuedItems)
                {
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "CheckUploadStatus";
                    sqlCmd.Parameters["QueueID"].Value = aQueuedCase.Id;
                    if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
                    try
                    {
                        sqlCmd.ExecuteNonQuery();
                        if ((int)sqlCmd.Parameters["RowsUpdated"].Value == 0)
                        {
                            continue;  // If not updated row by SP considering row locking then go for next case
                        }
                    }


    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.


    • Edited by Microsoftee Sunday, September 15, 2013 12:17 PM unrelated code removed
    Sunday, September 15, 2013 12:15 PM
  • In my opinion, both the proc and code should work fine based on the information provided here.  Also, I don't see any need for SERIALIZABLE; the default READ_COMMITTED without hints should do the job:

    CREATE PROCEDURE [dbo].[CheckUploadStatus] 
    	@QueueID int, 
    	@RowsUpdated int OUTPUT
    AS
    
    UPDATE dbo.CasesQueue
    SET UploadStarted=1, UploadStartedAt=GETDATE() 
    WHERE id = @QueueID and UploadStarted=0;
    
    SELECT @RowsUpdated = @@ROWCOUNT;
    GO

    If you are experiencing a problem, additional logging on the application side will facilitate troubleshooting.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Dean Savović Sunday, September 15, 2013 5:39 PM
    Sunday, September 15, 2013 1:34 PM
  • This is more about C#, but I am not sure if ExecuteNonQuery supports output parameters. You may want to change it to ExecuteReader, and see if that makes any difference.

    And as I said before, you may want to prime the output parameter with a value other than 0 and 1.


    Gert-Jan

    Sunday, September 15, 2013 9:20 PM
  • Thanks all of you guys for support.

    As you guys suggested it doesn't need any table hint or anything, I changed it to query from SP and ExecuteNonQuery returns the number of rows affected based on result.

    I think it seems to be working, I'll verify later for concurrent access of record.

    It may be something wrong with OUTPUT parameter or nothing wrong at all the value I was checking to find concurrent access is duplicate, may be.


    Microsoftee www.informitsys.com
    Solved your problem, please click Mark As Answer it help others with similar problem find solution.

    Monday, September 16, 2013 9:11 AM