Transaction Isolation Levels

[Breck_Carter]

I have a process in my application where I need to search a large list (large for the program. We're talking about 2.5 million rows, which isn't large by database standards).

Anyway, I have rows in table #1 that need to be matched up with rows in the large table (table #2) and new rows inserted into a third table. I use an INSERT INTO ... SELECT statement to do this and it works. The thing is that this process can only be run on a row in table #1 once. So after the INSERT INTO ... SELECT runs, table #1 is updated and a column changed so they won't be selected again.

There is a bug, however.

The data in table #1 is collected by a sensor device and then inserted into that row. There is a process that runs as a Windows service and actually executes a stored procedure that does the INSERT INTO ... SELECT statement. The problem is that it is possible for a row to be inserted into Table #1 in the time between the INSERT INTO . . . SELECT finishes determining which rows need to be included and the UPDATE begins. When that happens, that row that is never included in the results of the INSERT INTO ... SELECT.

To fix this, we are considering setting the transaction isolation level to SNAPSHOT. I know that this comes with a performance hit. The question is how big of a hit is it? Does anyone who has any experience with it have any information they will share with us?

Thanks

Tony

[Breck_Carter]

There may be a way to structure your SQL commands so they work without having to bludgeon performance to death... but we need to see a more thorough description of the requirements... or, just show us the actual code that has the problem.

[VolkerBarth]

I don't have relevant experience, but asked a similar question lately:

http://sqlanywhere-forum.sap.com/questions/13194/how-does-the-row-versioning-for-snapshot-isolation-...

[MCMartin]

In your use case I think isolation level 3 would be better, as you want to prevent changes during your select. The snapshot will give you as it says a temp copy of the data, so it would prevent you from ommiting rows which are changed or deleted during your select. But as I understand the contrary is your problem, you select and might overlook newly inserted rows. This seems to fall in the class of phantom rows.

[VolkerBarth]

Yes, but snapshot isolation would as well prevent phantom rows: Both the insert ... select from #table1 and the update on #table 1 (if run in the same transaction) would see the same set of rows, so only the "seen" rows would be marked as "handled". - The next insert...select should then still list all rows that have been inserted after the begin of the first snapshot. - At least that is my idea of TonyV's description:)

And isolation level 3 might prevent the sensor device to deliver its data or would need an according buffer to wait until the phantom locks are released. That could or could not be a problem here...

[Former Member]

Volker Barth is correct in his interpretation of my issue. However, we ended up getting deadlocks when we tried isolation level 3. I'm adding an answer to indicate what we've done to get this to work.