Hello all! I'm new SQLite user. Sorry for my bad English, this is not my native language.
I try to understand which standard SQL isolation level correspond with transaction opened as BEGIN DEFERRED. I think this is "DIRTY READ", (quite dangerous level) because deferred transactions allow unrepeatable reads. I have two question: 1. Is my understanding correct, and deferred mode really dirty read? 2. Is deferred mode the default transaction mode in the current version of SQLite? --------------------------------------------------------------------- The next is explanation of my point of view. Consider this transaction: 1. BEGIN DEFERRED 2. SELECT Value FROM Deposits WHERE ClientID = 123 3. New deposit value calculated in C++ program: Value = Value - 100 (Suppose this is very complex calculation and cannot be embedded directly in UPDATE query) 4. UPDATE Deposits SET Deposit = <<here is new value>> WHERE ClientID = 123 5. COMMIT What happened if two such transactions T1 and T2 executed in parallel with same ClientID? If I understand correct, the next execution sequence is possible: 1. T1 executes BEGIN DEFERRED and get SHARED lock. 2. T2 executes BEGIN DEFERRED and get SHARED lock as well. 3. T1 executes SELECT and get deposit value (for example, 1000) 4. T2 executes SELECT and get the same deposit value (1000) 6. T1 acquires RESERVED lock, do UPDATE (set deposit value to 1000 - 100 = 900) and then performs COMMIT 7. T2 acquires RESERVED lock, do UPDATE (set deposit value to 1000 - 100 = 900) and then performs COMMIT. Result of T1 transaction is lost. The final deposit value is not 800, but 900, because transaction T2 performs unrepeatable read in its SELECT. Best regards, Alexander mailto:[EMAIL PROTECTED]
