Preventing concurrent inserts

Question

Preventing concurrent inserts

SQL Guy 1

SSCoach

Points: 15979
More actions
February 24, 2021 at 9:37 pm

#3849782
Hi all,
In this, very simplified scenario, we have a table with two fields and few records:
```
create table log_test1
(c1 int,
c2 int)
go

insert into log_test1
values 
(1,11),
(2,12)
go
```
In a stored procedure, we select max from one of the columns, add one, and insert back into the table:
```
select @v1 = max(c1)
from log_test1

set @v2 = @v1 + 1

insert into log_test1
select @v2, @v3
```
But the problem is, if at the same time another process will try to execute the same S.P., we will have inconsistent records, because the same value for @v2 will be used for different records.
How to prevent this? One thought is to enclose this code into a transaction, with setting trans isolation level to serializable. Will it work? Is there a better way to handle it, for example with a row-level locking?
Thanks

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Look at using OUTPUT instead of a SELECT/INSERT method (https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15)

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

As a thought, what about if you switched C1 to be an IDENTITY value or a SEQUENCE or a calculated column and not insert it via a stored procedure but have it auto-generated?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

begin transaction 

    select @v1 = max(c1)
    from log_test1 with(XLOCK)

    set @v2 = @v1 + 1

    insert into log_test1
    select @v2, @v3

commit transaction

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

Is this atomic in this situation? (My recollection is that it is NOT, without appropriate LOCK hint)

insert into log_test1
select(SELECT MAX(c1)+1 FROM log_test1)
, @v3

DesNorton SSC-Insane Points: 24386 More actions · Answer 5

Firstly, you need to correctly handle the NULL returned by MAX if the table is empty.

Secondly, take a look at this article on concurrency by Gail Shaw.

You will probably end up with something like this

INSERT INTO log_test1
SELECT  ISNULL( (SELECT MAX(c1)FROM log_test1 WITH (XLOCK, HOLDLOCK)), 0 ) + 1
      , @v3;

How to post data/code on a forum to get the best help.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 6

DesNorton wrote:

You will probably end up with something like this

That's the part that bothers me ... why a single statement is not, inherently, atomic.

Can I be sure I have covered all past, current and future bases with regards to "Hints" ...

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 7

Thanks all for replies.

Jonathan, your solution works. The only downside is that it creates blockings. But this is what was expected.

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

I have to agree with Brian... what is the problem with using something that guarantees you won't have a problem with concurrency such as an IDENTITY column or a SEQUENCE? For that matter, the content of this column shouldn't actually matter for anything other than uniquely identifying a row so why not use a GUID?

--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)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

GUIDS are big, fat and ugly... they bloat indexes. 😀

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

The Dixie Flatline wrote:

GUIDS are big, fat and ugly... they bloat indexes. 😀

Yep... but they work a whole lot better than trying to gerry rig a concurrent MAX solution for this and that's my point... even a GUID is a better solution that using MAX to "get the next id".

The really cool part is that you don't need a "Central Authority" to get one like you would with IDENTITY or SEQUENCE.

--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)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 11

Have you tried using a CREATE SEQUENCE construct?

Please post DDL and follow ANSI/ISO standards when asking for help.

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 12

No. And frankly, I never worked with it. Can you please give more details of how I can use it?

Thanks

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 13

https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/

Please post DDL and follow ANSI/ISO standards when asking for help.

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

SQL Guy 1 wrote:

Thanks all for replies.
Jonathan, your solution works. The only downside is that it creates blockings. But this is what was expected.

Ideally you would just have the first column in table log_test1 defined as an IDENTITY column. Then you wouldn't have to do anything. Your code would then just look like this:

insert into log_test1(col2)
select @v3

Is there any reason why you can't just change the column type on this table to be an identity?