Tell me more ×
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.
up vote 4 down vote favorite

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level:

1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete
2) 0:02 Thread B runs a query that returns the same 1000 rows
3) 0:05 Thread A updates the last 1 rows in this result set and commits them
4) 0:07 Thread B's query returns*

Depending on the isolation level, the result set in #4 will either contain Thread A's changes or it won't. Is the same true for UPDATES?

The following is an example scenario:

Thread A: UPDATE... set version=6 ... WHERE primary_key = 1234 AND version = 5
Thread B: UPDATE... set version=6 ... WHERE primary_key = 1234 AND version = 5

If both Thread A and Thread B enter their transactions at the same time, and Thread B performs its update after Thread A, will Thread B's update fail to update any rows or will it "see" the record with version 5 and therefore overwrite what Thread A wrote?

Does it depend on the database? e.g. Oracle vs MySql vs PostgreSQL?

share|improve this question
have you tried it? if not, perhaps you should, and report back with your findings. if it doesn't work the way you want someone will figure out a solution. – Max Vernon Nov 17 '12 at 19:31
It would seem like a straight forward question that someone on dba.stackexchange.com could easily/quickly answer based on their knowledge an experience. – BestPractices Nov 18 '12 at 0:16
2  
For PostgreSQL you can find all the details in the manual here. An isolation level generally applies to all SQL commands. – Erwin Brandstetter Nov 18 '12 at 1:00
This was cross-posted from stackoverflow.com/q/13431552/398670 . Please do not do that, or at least link back to the original copy. – Craig Ringer Nov 18 '12 at 9:48
Please do not cross post. We can move questions from SO or other sites if needed but having multiple copies causes a lot of issues. – JNK♦ Nov 18 '12 at 11:45
show 3 more comments

closed as not a real question by JNK♦ Nov 18 '12 at 11:45

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

1 Answer

up vote 2 down vote

In MS SQL Server I can say that it depends on your isolation level. If you set it more similar to Oracle by making it 'snapshot isolation', the last row that thread A is trying to update will have the 'before' value copied to tempdb, and all reads will read that while it is being updated via a row lock.

If you have the default isolation level of read committed, it will lock that row (or in some cases page) blocking your reader from that particular row or page while it updates it. This is unless you specify SET ISOLATION LEVEL READ UNCOMMITTED or put a (NoLock) hint on the FROM clause of the query, thus allowing dirty reads.

It all has to do with the Isolation level in ACID, and it's the only ACID property that you have control over in MS SQL Server.

share|improve this answer
2  
Mark, updates absolutely do depend on the isolation level, the whole point of isolation levels is to in fact deal with UPDATES/INSERTS. As mentioned in the post "Read Committed Snapshot Isolation" will create a snapshot of the row while the storage engine requests to lock the row to update/modify it. This way the update does not block the reader, and the reader avoids dirty reads, knowing that they got the last value. You also have options to wrap it within a transaction to ensure consistency on the transaction level. :) – Ali Razeghi Nov 18 '12 at 4:13
I cannot see an update being affected at all by any isolation level. An update is an update - it either updates or fails. Reads on the other hand, are affected by isolation levels. – Max Vernon Nov 18 '12 at 4:51
@MaxVernon: an update does a search to locate rows to update. That part is as much subject to isolation levels as a plain select. Saying that updates & deletes aren't affected by isolation levels doesn't make sense. – Mat Nov 18 '12 at 10:27
@SQL-Learner Reversed my vote and happy to admit I learnt something new. The great example I found was this easy to reproduce script. Previously I had it drummed in to my head that the demonstrated scenario would fail, as updates locks would be taken at the point of read. But for snapshot in SQL Server that most definitely is not the case. – Mark Storey-Smith Nov 18 '12 at 13:49
No problem guys, I'm here to learn from everyone as well. I'm glad I was able to contribute something to this site. Thanks for the subjective reviews. – Ali Razeghi Nov 18 '12 at 16:41
show 1 more comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.