May 18, 2017 at 8:11 am
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.
| NoteID | CreatedDatetime | RowVersion | PersonID |
| 21111564 | 2017-05-12 16:31:50.810 | 0x00000001889D0911 | 99362 |
| 21111565 | 2017-05-12 16:31:50.810 | 0x00000001889D0912 | 99362 |
| 21005020 | 2017-05-06 01:25:35.840 | 0x0000000187D2591F | 103654 |
| 21005021 | 2017-05-06 01:25:35.840 | 0x0000000187D2591E | 103654 |
| 20464400 | 2017-04-02 09:33:23.093 | 0x0000000183CCB0F9 | 114666 |
| 20464401 | 2017-04-02 09:33:23.093 | 0x0000000183CCB0FB | 114666 |
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
May 18, 2017 at 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;
May 18, 2017 at 8:27 am
Luis Cazares - Thursday, May 18, 2017 8:22 AMWhy 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?
May 18, 2017 at 8:34 am
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.
May 18, 2017 at 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
May 18, 2017 at 9:19 am
Luis Cazares - Thursday, May 18, 2017 9:11 AMI 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