December 23, 2009 at 12:21 pm
Hello,
We're having a .Net application which invoke multiple stored procedure within a transaction using Snapshot Isolation level which return data related between the calls. Snapshot Isolation guaranteed that each stored procedure's fetched data will be consistent with other fetch stored procedures within the ongoing transaction. I've summarized how it is working.
Open Transaction with Snapshot Isolation Level
dataset1 = call Sproc1
logic using dataset1 data
dataset2 = call Sproc2
logic using dataset1 & dataset2 data
dataset3 = call Sproc3
Close Transaction
logic using dataset1 & dataset2 & dataset3 data
Anytime a replication server could be initialize (from fresh) while the main server's running. When the initial snapshot's being done by the transactional replication engine, if a query using a snapshot transaction level is run at the same time we're facing:
Snapshot isolation transaction failed in database 'theDB' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.
I've check on msdn to find whatever clue might be available and found this:
This error can occur if you are querying metadata under snapshot isolation and there is a concurrent DDL statement that updates the metadata that is being accessed under snapshot isolation. SQL Server does not support versioning of metadata.
What's funny is all stored procedures which are run inside the snapshot isolation level does only basic DML operations. No DDL are being made or metadata queried explicitly.
For testing purposes, if I remove the Snapshot Isolation level transaction scope and replace it with the default one, read committed; as you might guessed the error while setuping the replication initial snapshot does not fire anymore and everything's working well.
Modifying the code to query only one SP and remove Snapshot Isolation level by linking data within a single SP would be the prefer scenario but it cannot be achieve easily and is discarded.
Changing the replication model cannot also be easily be changes as it do exactly what we need.
Is there a way to prevent the snapshot isolation level error while setuping transactional replication with this scenario?
Thank you!
January 12, 2010 at 9:31 am
I've narrowed the issue.
It happen while the publisher's creating the articles to publish (before creating the initial database snapshot).
While this being done, if some code using snapshot isolation level is run which access a table being used for creating an article, the issue will occurs.
Why can't we used the snapshot isolation level while an article being created?
January 12, 2010 at 11:38 am
What snapshot initialization mode are you using?
* Noel
January 12, 2010 at 11:49 am
Are you talking about the transaction snapshot isolation level?
Or the replication snapshot?
For the former it is the isolation level when an SP's running a transaction.
The latter I'm not aware of multiple snapshot for initializing a publication. It's either a snapshot or by using a backup (we're using a db snapshot for initializing)
I'm not sure I understand correctly what you need to help me.
January 12, 2010 at 12:03 pm
I was talking about replication snapshot.
If you are initializing using db backup you are then *not* having problems at "replication initial snapshot" time.
Because the snapshot is actually never run!
Now you said that you add articles... Why are you constantly adding articles ? that should normally be a seldom performed operation.
* Noel
January 12, 2010 at 12:17 pm
Ok now I understand.
The issue occurs before the initial replication snapshot is being done. You can request to not run the snapshot when creating the replication which I did to understand on which step exactly the issue occurred.
Therefore using a backup instead of using the db snapshot will not solve the issue I'm having because it fail before that step. The conflict is between the time articles are being created and the query run. Once all articles are being created, no issue occurs anymore.
You're right on this point: it should be quite seldom initializing a replication, but even if it's seldom that kind of issue must not occur when, at any time, a request to initialize a replication is done while applications are running.
January 12, 2010 at 12:22 pm
Again: why are you "adding" articles multiple times ?
* Noel
January 12, 2010 at 12:33 pm
I'm testing a case when the initial replication is being requested. Articles are added once.
I believe I understand why you're asking that. As a DBA you have control and know what is happening to the SQL box you're working on. Therefore upon any incident of this type can be solved immediately and without much impacts.
However I'm working on a solution which will be deployed by it's own and run by it's own where no DBA will never have a look upon.
When and where the replication will be request is unknown. This a kind of blackbox were deploying and those who are using it might request at some point, whatever they want to initialize a replication. Therefore it must run, without any outside help.
January 12, 2010 at 1:00 pm
As far as I know there is no control (at user level) on how sp_addarticle works from transaction isolation level perspective.
Adding articles to a publication does generates some DDL events at the metadata level and there is no way around that.
Once those articles are published re-synchronizing while there is activity in the db should not be a problem as long as you are using 'db backup' or 'database snapshot' initialization.
IF you want to dynamically do both you will have to somehow find a way to "pause" the application while your publication is setup.
That error you are facing is an intrinsic limitation of the snapshot isolation level and you are forced to deal with it.
There are many other limitations involving triggers, output clauses etc that are also problematic and you should design with them in mind.
* Noel
January 12, 2010 at 1:11 pm
Adding articles to a publication does generates some DDL events at the metadata level and there is no way around that.
I've read tons of documents and none ever tell that. I had to face that while testing the solution and hence posting here. Experience some sort of.
Yes it is what I've notice from the error something was being done with metadata.
Other objects like triggers etc have been dealt with already and they are running properly.
Yes both would need to be done dynamically. It's not an option of pausing the application. However I've come up with a few workarounds in case we are forced by this technical limitation.
Still I wish to know why, what, which metadata are used while creating articles. Curiosity at this point only but I wish to know 🙂
Thks for your help
January 12, 2010 at 1:45 pm
This is one of the things that you can verify easily in 2008.
When you create a publication on a database and replicate ddl statements is "on" your database gets 4 DDL triggers created by replication code:
tr_MStran_alterschemaonly
tr_MStran_altertable
tr_MStran_altertrigger
tr_MStran_alterview
It is easy to guess what they do and the fact that they get "created" at addpublication time which already implies metadata changes.
I could go deeper into the sys.sp_articlecolumn but it is not necessary. It is easy to see that sysarticles etc get created as well as the synchobj views that may or may not be necessary ... anyway *many* metadata operations take place while all the code in addpublication and addarticle is being executed.
Hopefully your curiosity has been satisfied a little 😉
* Noel
January 12, 2010 at 1:54 pm
Thks! It will help once I'll go take a look at those.
I believe I have an idea what's happening there now but still I will go check. For the pleasure of it!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply