June 16, 2016 at 3:39 am
Hello,
Can anyone explain why the following queries lead to different results ?
Testing environment:
CREATE TABLE jb_test
(
Id INT IDENTITY(1, 1) PRIMARY KEY,
ColA INT,
ColB INT
)
GO
BEGIN TRAN
DECLARE @cA INT
SET @cA = 1
WHILE @cA < 1000000
BEGIN
INSERT INTO jb_test (ColA, ColB)
SELECT @cA, @cA
SET @cA = @cA + 1
END
COMMIT
GO
CREATE TRIGGER TI_jb_test_IU ON jb_test INSTEAD OF INSERT, UPDATE NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT OFF
UPDATE jb_test
SET ColA = i.ColA, ColB = i.ColB
FROM inserted AS i
WHERE jb_test.Id = i.Id
END
GO
CREATE PROCEDURE p_jb_test2
@cId INT
AS
BEGIN
BEGIN TRY
CREATE TABLE #tmp
(
Id INT
)
INSERT INTO #tmp (Id)
SELECT @cId
UPDATE jb_test
SET ColB = -2
FROM #tmp AS t
WHERE t.Id = jb_test.Id
RETURN 0
END TRY
BEGIN CATCH
RETURN(99)
END CATCH
END
GO
CREATE PROCEDURE p_jb_test
@cId INT
AS
BEGIN
BEGIN TRY
UPDATE jb_test
SET ColB = -2
WHERE Id = @cId
RETURN 0
END TRY
BEGIN CATCH
RETURN(99)
END CATCH
END
GO
Check
SELECT * FROM jb_test WHERE Id = 100
ColA = 100
ColB = 100
First Test
First connection
BEGIN TRAN
UPDATE jb_test
SET ColA = -1
WHERE Id = 100
Second connection
BEGIN TRAN
EXEC p_jb_test2 @cId = 100
First connection
COMMIT
Second connection
COMMIT
Check
SELECT * FROM jb_test WHERE Id = 100
ColA = 100 ???
ColB = -2
Second Test
Restore values
UPDATE jb_test
SET ColA = 100, ColB = 100
WHERE Id = 100
Check
SELECT * FROM jb_test WHERE Id = 100
ColA = 100
ColB = 100
First connection
BEGIN TRAN
UPDATE jb_test
SET ColA = -1
WHERE Id = 100
Second connection
BEGIN TRAN
EXEC p_jb_test @cId = 100
First connection
COMMIT
Second connection
COMMIT
Check
SELECT * FROM jb_test WHERE Id = 100
ColA = -1 OK
ColB = -2
June 16, 2016 at 4:27 am
Both tests should produce the same results, are you 100% sure you executed the statements in the same order as listed in your post?
June 16, 2016 at 4:34 am
Terje Hermanseter (6/16/2016)
Both tests should produce the same results, are you 100% sure you executed the statements in the same order as listed in your post?
Unable to repeat OP's results either.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2016 at 4:51 am
Yes, I am sure.
Maybe we have different database isolation ?
I use READ_COMMITED_SNAPSHOT isolation
DECLARE @sql AS VARCHAR(MAX)
SET @sql = 'ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@sql)
SET @sql = 'ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON'
EXEC (@sql)
SET @sql = 'ALTER DATABASE '+ DB_NAME()+' SET MULTI_USER'
EXEC (@sql)
June 16, 2016 at 5:35 am
siarsko (6/16/2016)
Yes, I am sure.Maybe we have different database isolation ?
I use READ_COMMITED_SNAPSHOT isolation
[/code]
Yep, that's it! My test database has READ_COMMITED_SNAPSHOT set to OFF. I changed it to ON, ran the tests again, and got the same results as you.
June 16, 2016 at 5:51 am
Super 🙂
May I ask why that happened?
June 16, 2016 at 6:13 am
siarsko (6/16/2016)
Super 🙂May I ask why that happened?
That's beyond my comprehesion.
Somehow it seems like the second commit "rolls back" the result of the first commit when READ_COMMITED_SNAPSHOT is set to ON.
You'll need a much bigger expert on SQL Server to explain this behaviour than I'm capable of 😛
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply