SQL Server SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Question

In the GL journal voucher table I set identity "JV_ID" as bigint datatype with identity (so I can avoid getting max (JV_ID) then adding +1) , but in my database journal voucher table ID's should start with year ID as following (202100001,202100002,202200001,202200002), then I found threat talking about SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in SQL server, So my questions:

Is setting isolation Level will really avoid duplication in getting same max ID in stored procedures?

How could I write the SP for the following table data:

JV_ID          NVARCHAR 25
JV_YEAR        NVARCHAR 4
JV_DATE        DateTime
JV_TOT         Money
JV_USER        INT

I found this code in this thread link How to get Maximum ID value before creating a new record?

CREATE PROCEDURE usp_CreateNewIssue
    @catagoryId int,
    @issueId int OUTPUT,
    @issueNumber int OUTPUT
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
        declare @maxId int        
        declare @newRow table (IssueID int NOT NULL, IssueNumber int NOT NULL)

        select @maxId = MAX(IssueNumber) from Issue where CategoryID = @catagoryId

        Insert Into Issue (CategoryID, IssueNumber) values (@catagoryId, @maxId + 1)
           OUTPUT INSERTED.IssueID, INSERTED.IssueNumber 
           INTO @newRow

        select @issueId = IssueID, @issueNumber = IssueNumber from @newRow
    COMMIT TRANSACTION
END

Some interesting [read](https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/) for you maybe — GuidoG, Jan 14 '23 at 07:04
Do you have something against using an identity or sequence? — Stu, Jan 14 '23 at 09:43
Can you elaborate on "threat talking"? Perhaps what you found isn't a concern or is a performance issue that is acceptable in your environment. — HABO, Jan 14 '23 at 18:18

score 0 · Answer 1 · answered Jan 14 '23 at 16:14

0

No SERIALIZABLE won't help here because the read isn't done with an exclusive lock. You need to add a lock hint to the SELECT.

CREATE PROCEDURE usp_CreateNewIssue
    @catagoryId int,
    @issueId int OUTPUT,
    @issueNumber int OUTPUT
AS
BEGIN
    BEGIN TRANSACTION
        declare @maxId int        
        declare @newRow table (IssueID int NOT NULL, IssueNumber int NOT NULL)

        select @maxId = MAX(IssueNumber) from Issue with (updlock) where CategoryID = @catagoryId

        Insert Into Issue (CategoryID, IssueNumber) values (@catagoryId, @maxId + 1)
           OUTPUT INSERTED.IssueID, INSERTED.IssueNumber 
           INTO @newRow

        select @issueId = IssueID, @issueNumber = IssueNumber from @newRow
    COMMIT TRANSACTION
END

answered Jan 14 '23 at 16:14

David Browne - Microsoft

80,331
6
39
67

Is Updlock really enough? We always used WITH(HOLDLOCK, XLOCK) to avoid dirty reads from others – siggemannen Jan 14 '23 at 20:43
1

HOLDLOCK is needed if there are no rows with the category to add empty range locking. XLOCK will block S locks, but U locks wont. So UPDLOCK is enough, since other sessions wanting a U lock or X lock will be blocked. Other sessions can read, so long as they aren't reading with the intent to update. – David Browne - Microsoft Jan 14 '23 at 20:44
Hmm, ok, i see, good point, if all sessions use same procedure that has UPDLOCK it will work. But if other session reads MAX id without that hint at the same time you're reading it, they will get same value. Perhaps belts and suspenders are better in this case – siggemannen Jan 14 '23 at 20:53
Sure. But if another session reads the MAX(ID) for some reason other than generating a new ID, what's the harm? If they had queried 1ms earlier, or if using SNAPSHOT or RCSI row versioning, they would have read the value without getting blocked. – David Browne - Microsoft Jan 14 '23 at 21:42
@DavidBrowne-Microsoft Mr. David here is my problem point with (updlock) it will return error to the other user and I'm not professional enough to handle those errors, specially when I read about "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" I thought that it will set sequence for each stored procedure extension execution without errors ! – A.J Jan 16 '23 at 04:52
No errors. Should just block the other session briefly. – David Browne - Microsoft Jan 16 '23 at 12:20

SQL Server SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

1 Answers1