Atomic Transaction.? - Not Quite......

  • Hi All,

    Can I pick your brains please.

    Have a 3rd party application (Java) calling stored procs to utilise our DB.

    To say that it keeps me busy is an understatement.

    A particular occurrence I can't get my head around is this, this part of a proc -

            BEGIN TRANSACTION
                IF (SELECT NoteID FROM dbo.Note WHERE PersonID = @PersonID) IS NOT NULL
                    BEGIN
                        UPDATE    dbo.Note
                        SET        [Description] = @Description
                        WHERE    PersonID = @PersonID
                    END 
                ELSE
                    BEGIN  
                        INSERT    INTO dbo.Note(PersonID, [Description], CreatedDatetime)
                        VALUES (@PersonID, @Description, GETDATE())
                    END            
            COMMIT TRANSACTION;
     
            
    At some point something managed to populate our table with two notes for the same person and as you will see from the CreatedDateTime column they were created at eaxcatly the same time, although the RowVersion is sequential.

         

    NoteIDCreatedDatetimeRowVersionPersonID
    211115642017-05-12 16:31:50.8100x00000001889D091199362
    211115652017-05-12 16:31:50.8100x00000001889D091299362
    210050202017-05-06 01:25:35.8400x0000000187D2591F103654
    210050212017-05-06 01:25:35.8400x0000000187D2591E103654
    204644002017-04-02 09:33:23.0930x0000000183CCB0F9114666
    204644012017-04-02 09:33:23.0930x0000000183CCB0FB114666

    Is is possible that the two calls of the stored procedure were called at precisely the same time to the point that the a row didn't exist for the person as the code evaluated the IF statement ?

    The Note table scripts out as -

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Note](
        [NoteID] [INT] IDENTITY(1,1) NOT NULL,
        [Description] [NVARCHAR](1024) NULL,
        [CreatedDatetime] [DATETIME] NULL,
        [RowVersion] [TIMESTAMP] NULL,
        [PersonID] [INT] NULL,
    CONSTRAINT [PK_Note] PRIMARY KEY CLUSTERED
    (
        [NoteID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Note] WITH CHECK ADD CONSTRAINT [FK_Note_Person] FOREIGN KEY([PersonID])
    REFERENCES [dbo].[Person] ([PersonID])
    GO

    ALTER TABLE [dbo].[Note] CHECK CONSTRAINT [FK_Note_Person]
    GO

    Names have been removed to protect their identity and at present I'm unable to give an explanation

    Thanks

    Stephen

  • Why would you only have one note for each person? That seems illogical.
    However, here's an alternative that should handle concurrency in a better way.

    BEGIN TRANSACTION
      UPDATE  dbo.Note
      SET   [Description] = @Description
      WHERE  PersonID = @PersonID;

      INSERT INTO dbo.Note(PersonID, [Description], CreatedDatetime)
      SELECT @PersonID, @Description, GETDATE()
      WHERE NOT EXISTS (SELECT NoteID FROM dbo.Note WHERE PersonID = @PersonID);
    COMMIT TRANSACTION;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Thursday, May 18, 2017 8:22 AM

    Why would you only have one note for each person? That seems illogical.
    However, here's an alternative that should handle concurrency in a better way.

    BEGIN TRANSACTION
      UPDATE  dbo.Note
      SET   [Description] = @Description
      WHERE  PersonID = @PersonID;

      INSERT INTO dbo.Note(PersonID, [Description], CreatedDatetime)
      SELECT @PersonID, @Description, GETDATE()
      WHERE NOT EXISTS (SELECT NoteID FROM dbo.Note WHERE PersonID = @PersonID);
    COMMIT TRANSACTION;

    Bear in mind the point about 3rd party application, the area of their application that this is utilised for only has one note that can be updated. Not our operational requirement.
    Thanks for the alternative, still am curious as to 'how' this happened?

  • Just to add to what Luis said, the trick is that the SELECT to check for existence is only taking shared locks. 

    Nothing prevents two instances of that code from running at the same time, seeing no rows with a particular person ID (because shared locks don't conflict), and both entering the execution branch with the INSERT.

    What you're seeing is not actually a failure of atomicity (each transaction is indeed "all or nothing" and either the entire transaction fails or the entire transaction succeeds).

    You're seeing that isolation comes in degrees, and in this case you're seeing results that wouldn't be possible if each transaction were run in isolation in some particular order.

    The default transaction isolation level of read committed allows such things.

    To get the behavior you're expecting you'd have to do something like Luis posted, which makes sure the locks taken are such that the phenomenon you observed doesn't happen, or by using a more strict isolation level (of course, that cure might be worse than the disease; it all depends on your requirements).

    Cheers!

    EDIT: Cleared up some weird grammatical choices in the original.

  • I forgot to mention something.
    If you really need the PersonID to be unique, then add a UNIQUE constraint to that column

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Thursday, May 18, 2017 9:11 AM

    I forgot to mention something.
    If you really need the PersonID to be unique, then add a UNIQUE constraint to that column

    +1

Viewing 6 posts - 1 through 5 (of 5 total)

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