blocking due to cuncurrency problem.

Question

Post reply

blocking due to cuncurrency problem.

rajemessage 14195

Hall of Fame

Points: 3315
More actions
June 10, 2024 at 6:18 am

#4427345
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

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

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

Perry Whittle SSC Guru Points: 234011 More actions · Answer 1

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" 😉

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 3

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)

tung858 SSC Veteran Points: 238 More actions · Answer 4

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

ScottPletcher SSC Guru Points: 101012 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101012 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 8

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)

Perry Whittle SSC Guru Points: 234011 More actions · Answer 9

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" 😉

Ken McKelvey SSCoach Points: 18811 More actions · Answer 10

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.

This reply was modified 1 month, 2 weeks ago by Ken McKelvey.

Ken McKelvey SSCoach Points: 18811 More actions · Answer 11

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?

Jonathan AC Roberts SSCoach Points: 18302 More actions · Answer 12

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;

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 13

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)

Jonathan AC Roberts SSCoach Points: 18302 More actions · Answer 14

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
;