Hello, I've been reading about dirty reads but I did not find any clear explanation about what 'shape' a dirty read has. The impression I got is that a dirty read is a row which can be considered 'consistent' at the moment it's been written, but that can be rolled back and therefore could disappear. When I say 'consistent' I mean that it will always contain exactly the data that it would contain at the end of the transaction if ever committed. So, a dirty read is something that can disappear, but if it will not be rolled back it will be exactly as it was when it appeared (unless any other command modifies it, of course). Also, a dirty read will never represent something (like a sort of temporary or invalid record) which is not a result of an explicit command. Is that right?
Thanks, probably it's a stupid question but the word 'dirty' is quite scary and makes me think that really weird things can happen (like half-baked rows, duplicated rows...).
asked May 18, 2012 at 10:15 AM in Default
A dirty read is one that neither attempts to obtain locks, or honour any held locks on the data that you are reading. This means not only could you be reading data that is being modified or deleted, but you could also miss data as it is is being inserted, or moved around a page.
In your example you could still have issues if the inserting transaction caused a page to fill, and then split, then your read could either not read some rows that have been moved, or even read rows twice.
The phrase 'dirty' simply means that what you get as a result may not reflect what is actually in the database.
answered May 18, 2012 at 10:28 AM
Kev Riley ♦♦