BUG: transaction always SERIALIZABLE despite setting

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

BUG: transaction always SERIALIZABLE despite setting

Postby dbco_ew » Wed Dec 04, 2013 11:03 am

Using SQL compare 10.5.0.611 I always get the line
Code: Select all
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
[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: Select all
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: Select all
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
to
Code: Select all
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.
dbco_ew
 
Posts: 5
Joined: Wed Dec 04, 2013 10:48 am

Postby Chris Auckland » Fri Dec 06, 2013 3:25 pm

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby dbco_ew » Mon Dec 09, 2013 8:17 am

I thought I missed something in the original post ;).

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.
dbco_ew
 
Posts: 5
Joined: Wed Dec 04, 2013 10:48 am

Postby Chris Auckland » Mon Dec 09, 2013 11:16 am

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby dbco_ew » Mon Dec 09, 2013 12:18 pm

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.
dbco_ew
 
Posts: 5
Joined: Wed Dec 04, 2013 10:48 am

Postby Chris Auckland » Mon Dec 09, 2013 1:58 pm

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby dbco_ew » Tue Dec 10, 2013 9:50 am

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: Select all
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: Select all
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.
dbco_ew
 
Posts: 5
Joined: Wed Dec 04, 2013 10:48 am

Postby Chris Auckland » Tue Dec 10, 2013 10:11 am

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby dbco_ew » Tue Dec 10, 2013 10:38 am

Made a snapshot, but I cannot get into the ticket.
dbco_ew
 
Posts: 5
Joined: Wed Dec 04, 2013 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest

cron