March 5, 2015 at 6:06 am
I vaguely remember reading somewhere that all distributed transactions are executed at Serializable Isolation Level "under the covers."
1. Is this true?
2. What does "under the covers" mean in this case; i.e. will I not see the isolation level represented accurately in requests?
Jared
CE - Microsoft
March 5, 2015 at 8:14 am
Looks like you're right. The only reference that I could find is this page in Paul Randal's blog: http://www.sqlskills.com/blogs/paul/worrying-wait-type/
-- Gianluca Sartori
March 5, 2015 at 8:37 am
I performed some tests and it looks like it's not completely true (as far as I can tell):
-- SETUP:
-- 1. Create a linked server
-- 2. Execute this at both servers:
--SELECT *
--INTO tempdb.dbo.spt_values
--FROM master.dbo.spt_values
-- TEST:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET XACT_ABORT ON;
BEGIN TRAN
-- Transaction isolation level before the transaction
-- is promoted to distributed transaction
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
UPDATE v
SET name = 'rpc'
FROM tempdb.dbo.spt_values AS v
WHERE name = 'rpc'
UPDATE v
SET name = 'rpc'
FROM [SQLCLP01\SQL2012].tempdb.dbo.spt_values AS v
WHERE name = 'rpc'
-- Transaction isolation level after the transaction
-- is promoted to distributed transaction
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
-- DON'T COMMIT OR ROLLBACK
-- Connect to the server referenced in the linked server setup
-- Run this:
SELECT es.transaction_isolation_level
FROM sys.dm_tran_active_transactions AS at
INNER JOIN sys.dm_tran_session_transactions AS st
ON at.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS es
ON st.session_id = es.session_id
WHERE transaction_type = 4
In all cases, the transaction isolation level for the session is always 2 (READ COMMITTED).
I have no idea what Paul meant with that statement.
-- Gianluca Sartori
March 9, 2015 at 2:17 am
Very interesting. What worries me is that I could find no documentation about it, just a tiny statement in Paul's post. Such an important information deserves better advertising IMHO
-- Gianluca Sartori
March 10, 2015 at 11:08 am
I set up a test, and I can't even get Serializable to work right:
Connection 1 on serverA:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT * FROM serverB.database.schema.table WHERE idCol = 1;
Returns 1 row
Connection 2 on serverB:
USE database
UPDATE schema.table
SET col2 = 1 WHERE idCol = 1
(1 row(s) affected)
When I change BEGIN TRAN to BEGIN DISTRIBUTED TRANSACTION, same thing... no blocking or waiting.
Jared
CE - Microsoft
March 10, 2015 at 11:49 am
SQLKnowItAll (3/10/2015)
I set up a test, and I can't even get Serializable to work right:Connection 1 on serverA:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRAN
SELECT * FROM serverB.database.schema.table WHERE idCol = 1;
Returns 1 row
Connection 2 on serverB:
USE databaseUPDATE schema.table
SET col2 = 1 WHERE idCol = 1
(1 row(s) affected)
When I change BEGIN TRAN to BEGIN DISTRIBUTED TRANSACTION, same thing... no blocking or waiting.
Change in code to get Serializable to work. Then I tested Distributed transaction and it did not:
Connection 1 on serverA:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT * FROM serverB.database.schema.table WHERE col2 = 1;
Returns 1 row
Connection 2 on serverB:
USE database
UPDATE schema.table
SET col2 = 2 WHERE col2 = 1
blocked! This is what we expected!
Connection 1 on serverA:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN DISTRIBUTED TRAN
SELECT * FROM serverB.database.schema.table WHERE col2 = 1;
Returns 1 row
Connection 2 on serverB:
USE database
UPDATE schema.table
SET col2 = 2 WHERE col2 = 1
(1 row(s) affected) Hmm... If this was serializable, it should be blocked. Right?
Jared
CE - Microsoft
March 10, 2015 at 4:02 pm
Exactly. Maybe we should ask Paul Randal to chime in. Let me ping him and see if he can drop by.
-- Gianluca Sartori
March 10, 2015 at 6:03 pm
So, I asked a few friends and turns out that if you don't set the transaction isolation level explicitly, it takes the default, which is read committed as far as the transaction is local and then escalates to serializable as soon as the transaction becomes distributed.
I don't have a SQL instance to try now, but that's how it should work.
Apparently, it depends on the DTC, which defaults to serializable.
More info here: http://stackoverflow.com/questions/11292763/why-is-system-transactions-transactionscope-default-isolationlevel-serializable
-- Gianluca Sartori
March 11, 2015 at 5:43 am
spaghettidba (3/10/2015)
So, I asked a few friends and turns out that if you don't set the transaction isolation level explicitly, it takes the default, which is read committed as far as the transaction is local and then escalates to serializable as soon as the transaction becomes distributed.I don't have a SQL instance to try now, but that's how it should work.
Apparently, it depends on the DTC, which defaults to serializable.
More info here: http://stackoverflow.com/questions/11292763/why-is-system-transactions-transactionscope-default-isolationlevel-serializable
Hmm... Even with not setting the level explicitly or defining the transaction as distributed, when I go the other server I can still update. I'll kepe playing to see if I can recreate this, but so far I am at a loss...
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply