blocking due to cuncurrency problem.

  • hi,

    CREATE TABLE [dbo].[bridge](
    bridgeid [bigint] NOT NULL,
    [idpool] bigint NULL,
    status int null
    CONSTRAINT [PK_bridge] PRIMARY KEY CLUSTERED

    (

    [bridgeid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    Go

    bridgeid is pk , and hardly four to 5 rows are there.

    idPool col has number like 1 we increment it and use it as id pool like following.

    below query is in small sp.

    DECLARE @intID BIGINT ,@BRIDGID BIGINT=1

    SELECT @intID = (idPool + 1)FROM IdConfiguration WHERE BRIDGID = @BRIDGID AND Status = 1

    UPDATE BRIDGE SET idPool = idPool + 1, idPool = @intID WHERE BRIDGID = @BRIDGID AND Status = 1

    Q1) the problem is the query blocks the whole table and in other transaction if i pass 2 in @BRIDGID

    then also it is blocked by the first query where i have passed 1 in @BRIDGID.

    yours sincerly

  • do you have more info on what this table is used for and how frequently its updated.

    for the idpool it sounds like its a value youre using elsewhere and maybe a sequence would be a better fit!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

    1. yes it is pk id pool, we pick up from this table and insert into our OLTP table's PK.

    2) sequence i can not use , becasue the id is generated based on contatication of siteoffices id + running id, which is kept in bridge table's idpool.

     

  • This is a classic "Get NextID" issue.

    It would also be nice if, when you posted a code example to explain your problem, that it would at least compile.  BridgID is NOT a valid column.

    The following code should solve this problem for you as well as making it much less likely for you to get a dupe.  It's the special "3 part update" (affectionately referred to as the "Quirky Update") in the MS documentation is IS a sanctioned use for it.  Put this code into a stored procedure for use.

    DECLARE  @intID    BIGINT
    ,@bridgeid BIGINT = 1
    ;
    UPDATE dbo.bridge
    SET @intID = idpool = idpool+1
    WHERE bridgeid = @bridgeid
    ;
    SELECT @intID
    ;

    https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql

    And,  yeah... I have used it for such a "NextID" issue to stop 400-6000 deadlocks per day from occurring at one company and to prevent duplicates at another.

    p.s.  SEND MONEY!  I'm ready to retire!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Try not to specify the idPool column twice in the same Update statement and get rid of the Select statement.

    The select statement where criteria is the same as the update filter criteria.

    Try just:

    UPDATE BRIDGE

    SET idPool = idPool + 1

    WHERE BRIDGID = @BRIDGID AND Status = 1

    Tung Dang
    Azure and SQL Server DBA Contractor / Consultant
    DataZip

  • Jeff Moden wrote:

    DECLARE  @intID    BIGINT
    ,@bridgeid BIGINT = 1
    ;
    UPDATE dbo.bridge
    SET @intID = idpool = idpool+1
    WHERE @bridgeid = 1
    ;

    Be careful,  that code would UPDATE every row in dbo.bridge.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • If you need to update PoolId based on another table, you should do the lookup in the same query as the UPDATE.  And there's no reason to update PoolId twice in the same UPDATE (I'm rather surprised SQL even allows that).  Also, you'll likely want an index on:

    dbo.IdConfiguration on ( Bridg[e]ID, Status ) INCLUDE ( idPool ) to avoid a table scan on IdConfig.

    It seems odd that you don't update the Config table also, but anyway, as below.  You need to make sure there's only one matching row in Config, of course.

    UPDATE B

    SET idPool = IC.Pool + 1

    FROM dbo.IdConfiguration IC

    INNER JOIN dbo.Bridge B ON B.BridgeID = @BridgeID

    WHERE IC.BridgeID = @BridgeID AND Status = 1 AND B.BridgeID = @BridgeID

     

    + 1, idPool = @intID WHERE BRIDGID = @BRIDGID AND Status = 1

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • tung858 wrote:

    Try not to specify the idPool column twice in the same Update statement and get rid of the Select statement.

    The select statement where criteria is the same as the update filter criteria.

    Try just:

    UPDATE BRIDGE SET idPool = idPool + 1 WHERE BRIDGID = @BRIDGID AND Status = 1

    That will change the value in the table but it will not return the value.  Setting the variable to the updated value will.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher wrote:

    Jeff Moden wrote:

    DECLARE  @intID    BIGINT
    ,@bridgeid BIGINT = 1
    ;
    UPDATE dbo.bridge
    SET @intID = idpool = idpool+1
    WHERE @bridgeid = 1
    ;

    Be careful,  that code would UPDATE every row in dbo.bridge.

    Oh,  lordy.  Quite right.  Thanks for the catch, Scott.  I've modified the code in my original post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Regardless of indexes with only a handful of rows a table scan is likely

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • rajemessage 14195 wrote:

    2) sequence i can not use , becasue the id is generated based on contatication of siteoffices id + running id, which is kept in bridge table's idpool. 

    You can still use a sequence. If you want a running id per office, just use a sequence for each office.

     

  • rajemessage 14195 wrote:

    bridgeid [bigint] NOT NULL,

    bridgeid is pk , and hardly four to 5 rows are there.

    If there are only a few rows, why is bridgeid a bigint?

  • To stop the table locking you could use a rowlock hint:

    -- Ensure we are using row-level locking
    SELECT @intID = (idPool + 1)
    FROM IdConfiguration WITH (ROWLOCK, UPDLOCK)
    WHERE BRIDGID = @BRIDGID AND Status = 1;

    -- Update the row with row-level lock
    UPDATE BRIDGE WITH (ROWLOCK)
    SET idPool = @intID
    WHERE BRIDGID = @BRIDGID AND Status = 1;
  • Jonathan AC Roberts wrote:

    To stop the table locking you could use a rowlock hint:

    -- Ensure we are using row-level locking
    SELECT @intID = (idPool + 1)
    FROM IdConfiguration WITH (ROWLOCK, UPDLOCK)
    WHERE BRIDGID = @BRIDGID AND Status = 1;

    -- Update the row with row-level lock
    UPDATE BRIDGE WITH (ROWLOCK)
    SET idPool = @intID
    WHERE BRIDGID = @BRIDGID AND Status = 1;

    While this "consume first then update" method is looks like it might be safer, it's not.  It's still not 100% safe because another process can sneak in between the SELECT and the UPDATE.  You don't want to put this in a transaction because it'll deadlock for sure.  I strongly recommend the "Quirky Update" method that I posted.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Jonathan AC Roberts wrote:

    To stop the table locking you could use a rowlock hint:

    -- Ensure we are using row-level locking
    SELECT @intID = (idPool + 1)
    FROM IdConfiguration WITH (ROWLOCK, UPDLOCK)
    WHERE BRIDGID = @BRIDGID AND Status = 1;

    -- Update the row with row-level lock
    UPDATE BRIDGE WITH (ROWLOCK)
    SET idPool = @intID
    WHERE BRIDGID = @BRIDGID AND Status = 1;

    While this "consume first then update" method is looks like it might be safer, it's not.  It's still not 100% safe because another process can sneak in between the SELECT and the UPDATE.  You don't want to put this in a transaction because it'll deadlock for sure.  I strongly recommend the "Quirky Update" method that I posted.

    Yes, your update could also be combined with a ROWLOCK hint:

    UPDATE dbo.bridge WITH (ROWLOCK)
    SET @intID = idpool = idpool + 1
    WHERE bridgeid = @BRIDGID
    AND status = 1
    ;

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply