[sqlite] Re: Isolation level of deferred transactions

[Igor Tandetnik]

Alexander J. Kozlovsky
<[EMAIL PROTECTED]> wrote:

T2 will try to acquire RESERVED lock of its own, but T1 is already
holding PENDING. At this point SQLite will return to the caller with
SQLITE_BUSY return code. The only way out of this situation is for
T2 to ROLLBACK and retry the transaction from the beginning.


Suppose next:

1. Transaction T2 perform complex calculation

2. T1 have time to release EXCLUSIVE lock ***before*** T2 try
  to acquire RESERVED lock


T1 won't be able to even acquire EXCLUSIVE lock, let alone release it, 
while T2 holds SHARED. Remember that T2 holds a SHARED lock from the 
beginning. The rest of your scenario thereby becomes moot.


Igor Tandetnik 

[sqlite] Re: Isolation level of deferred transactions

[Igor Tandetnik]

Alexander J. Kozlovsky wrote:

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 believe SQLite provides the highest isolation level, known as 
SERIALIZABLE in MS SQL Server. That is, no dirty reads, repeatable, no 
phantom rows.



I have two question:

1. Is my understanding correct, and deferred mode really dirty read?


I believe you are incorrect.


2. Is deferred mode the default transaction mode in the current
  version of SQLite?


Yes.


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 = <>
  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


RESERVED lock is insufficient to perform an update. To actually write 
data to the database file, the transaction needs to acquire EXCLUSIVE 
lock. But this is not possible while there is a SHARED lock in place. So 
T1 will acquire a PENDING lock and sit there waiting while all readers 
clear. As soon as this happens, it will grab EXCLUSIVE lock.



7. T2 acquires RESERVED lock, do UPDATE (set deposit value to
  1000 - 100 = 900) and then performs COMMIT.
  Result of T1 transaction is lost.


T2 will try to acquire RESERVED lock of its own, but T1 is already 
holding PENDING. At this point SQLite will return to the caller with 
SQLITE_BUSY return code. The only way out of this situation is for T2 to 
ROLLBACK and retry the transaction from the beginning.


For more details, see http://www.sqlite.org/lockingv3.html

Igor Tandetnik