Preventing concurrent inserts

  •  

    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

     

     

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • 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?

  • begin transaction 

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

    set @v2 = @v1 + 1

    insert into log_test1
    select @v2, @v3

    commit transaction
  • 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
  • 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;
  • 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" ...

  • Thanks all for replies.

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Have you tried using a CREATE SEQUENCE construct?

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

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

    Thanks

  • 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. 

  • 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?

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

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