blocking due to cuncurrency problem.

Question

hi,

I have a table "bridge"  where i keep bridgeid as pk , and hardly four to 5 rows are there

CREATE TABLE [dbo].[bridge](

bridgeid [bigint] NOT NULL,

[idpool] bigint 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

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

this 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

Answer 1

Hi @Rajesh Kumar Yadav

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

Please check if exists any constraints (such as unique constraints or foreign key constraints) on the columns being updated. Ensure that the value being set doesn't violate any constraints.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I don't understand your problem?

That short UPDATE statement may block the table for a millisecond (and less).