Red Gate forums :: View topic - BUG: transaction always SERIALIZABLE despite setting
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

BUG: transaction always SERIALIZABLE despite setting

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
dbco_ew



Joined: 04 Dec 2013
Posts: 5

PostPosted: Wed Dec 04, 2013 11:03 am    Post subject: BUG: transaction always SERIALIZABLE despite setting Reply with quote

Using SQL compare 10.5.0.611 I always get the line
Code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
in the migration script, despite the fact that I have set the Transaction Isolation Level to READ COMMITTED in Tools -> Application Options.
The used databases are all set to READ COMMITTED.

Normally, SERIALIZABLE is not a problem, but when I have a stored procedure that has a linked server queried in it, the transaction fails. It will fail with
Quote:
[7391] The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "XXXXXX" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "XXXXXX" returned message "The transaction manager has disabled its support for remote/network transactions.".


Turning on the "Do not use transactions in deployment scripts" option will not help. It will always fail for the stored procedure with the linked server in it and thus it will never actually deploy said stored procedure.

How to reproduce:
Make a simple stored procedure, like
Code:
CREATE PROC stp_simple_linked
AS
BEGIN
SELECT * FROM linked_server.database.dbo.table
END

And try to deploy this on a different server. Then the deployment will fail due to the setting as above. Copying the script to the clipboard, changing the line
Code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
to
Code:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
and the deployment is successful!

RECAP:
The problem is that the transaction isolation level setting from the SQL Compare is ignored and that in the migration script the isolation level is always set to SERIALIZABLE. I think this is due to the fact that the setting from SQL Compare is ignored and this, I think, is a bug.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Dec 06, 2013 3:25 pm    Post subject: Reply with quote

Thanks for your post.

It seems to respect the setting for me when I generate a script using SQL Compare 10.5.0.611.

The only thing I can think of is that for some reason you're unable to update the registry location where that setting is stored.

Can you check the 'TransactionIsolationLevel' string in:
HKEY_CURRENT_USER\Software\Red Gate\SQL Compare 10\UI

Can you make sure your account is able to modify this location, and that the string is set to 'READ COMMITTED'?
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
dbco_ew



Joined: 04 Dec 2013
Posts: 5

PostPosted: Mon Dec 09, 2013 8:17 am    Post subject: Reply with quote

I thought I missed something in the original post Wink.

Yes, when I check in the registry, then I see READ COMMITTED at the variable TransactionIsolationLevel at HKCU\Software\Red Gate\SQL Compare 10\UI. That is something I did check before posting this to the topic.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Mon Dec 09, 2013 11:16 am    Post subject: Reply with quote

Thanks for your reply.

Could you try uninstalling SQL Compare 10.5 and downgrade to 10.4?

There were a couple of problems with 10.5, so we pulled the release. This might be another example of it's brokenness.

Let me know how you get on.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
dbco_ew



Joined: 04 Dec 2013
Posts: 5

PostPosted: Mon Dec 09, 2013 12:18 pm    Post subject: Reply with quote

I cannot promise anything. It's on a production environment. We'll see what we can do. If we are going to downgrade, we'll let you know what the results are.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Mon Dec 09, 2013 1:58 pm    Post subject: Reply with quote

That's great. If you're unable to do that, would you be able to send me (through the support ticket we also have open) a SQL Compare snapshot of the source and target schema and I'll try and replicate the issue here?
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
dbco_ew



Joined: 04 Dec 2013
Posts: 5

PostPosted: Tue Dec 10, 2013 9:50 am    Post subject: Reply with quote

Fortunately, we've got a machine which has an older version of SQL Compare 10 on it (version 10.4.8.87; that's the version you recommended to downgrade to, right?), which has the exact same problem.

On that machine I cannot run regedit, but with the Powershell, I can confirm via
Code:
Get-ItemProperty 'HKCU:\Software\Red Gate\SQL Compare 10\UI\'
that the TransActionIsolationLevel is set to 'READ COMMITTED'.

Can you tell me exactly what you want/mean with a SQL Compare snapshot? I might be able to give you that information.

I think you can simply replicate it as follows. Create the following stored procedure:
Code:

CREATE PROC stp_problematic
AS
BEGIN
    SELECT * FROM linked_server.database.schema.table
END

That should be enough. Unless there are other settings you'd like to know.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Tue Dec 10, 2013 10:11 am    Post subject: Reply with quote

Thanks for your post.

Digging a little deeper, I think there can be situations where SQL Compare will override the 'transaction isolation level' and default to 'serializable'.

It would be great to try it with your snapshots and see if it's doing by design in this situation, or if it's doing for the wrong reasons (bug).

You can create the snapshots through the SQLCompare UI. File > Save snapshot
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
dbco_ew



Joined: 04 Dec 2013
Posts: 5

PostPosted: Tue Dec 10, 2013 10:38 am    Post subject: Reply with quote

Made a snapshot, but I cannot get into the ticket.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group