Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've got a problem with my transaction. Currently it runs in IsolationLevel.Snapshot. The action it does, can take some time depending on the amount of data, it has to import.

What it does:

  1. Create a table
  2. Update a row in another table with information about the new table.
  3. Inserts a lot of data in the newly created table

Sometimes it won't create a new table, but just insert data into an exisiting table. This is a user choice. The targeted tables have to be read/writeable at all times.

The problem is that a conflict occours radomly. I've might have been using the function around 50 or 70 times, at least, and 3 times, I've got a conflict caused by Snapshot isolation, several seconds after committing, and in a whole other table!

Exception message:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Table' directly or indirectly in database 'DatabaseName' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

I have checked, rechecked and double-checked and the transaction does not read from, insert into or update that table at any time.

The problem has occured on two different systems, both running Microsoft SQL Server 2012 Enterprise.

Has anyone experienced something similiar, or have some other experience that can be helpfull to me? Thanks :)


migration rejected from Feb 13 '14 at 1:11

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Paul White, Marian, RolandoMySQLDBA, Max Vernon, Mark Storey-Smith Feb 13 '14 at 1:11

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Are there triggers or foreign key cascading on any of these tables? Does the operation actually need to run in snapshot isolation? – Jon Seigel Nov 15 '13 at 17:52
Yes. Actually both the table I operate in, and the other that conflicts is using the same foreign key from a third table. Could this be the problem? No, I don't actually need to run in snapshot isolation, but it was the optimal way from what I read, when you both need to be able to read from the table meanwhile the transaction is running. Is there something more preferable? – Nicky Nov 18 '13 at 7:25
It depends on the data source you're loading into the table. If it's from other tables, then snapshot isolation has value; if it's from an external source, then it doesn't matter. Have you considered switching steps 2 and 3 so that what appears to be a shared row isn't exposed to issues for the entire duration of the operation? – Jon Seigel Nov 18 '13 at 14:45
It loads from an external source. So am I understanding you correct, that Snapshot Isolation have no value here? If so, what kind of transaction should I run instead, so that I can read/write to the same table simultaneously? Haven't tried switching 2 and 3, since it's finished code parts that I'm using. I could try that, but I don't think it will do any changes - it's not the table which is conflicting :( – Nicky Nov 18 '13 at 16:57
Please post your table schema, including all the foreign keys and cascading options, any triggers there are, etc. – Jon Seigel Nov 18 '13 at 17:06