Isolation level of deferred transactions

[Alexander J. Kozlovsky]

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:kozlovsky-***@public.gmane.org

[Igor Tandetnik]

Post by Alexander J. Kozlovsky
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.

Post by Alexander J. Kozlovsky
1. Is my understanding correct, and deferred mode really dirty read?

I believe you are incorrect.

Post by Alexander J. Kozlovsky
2. Is deferred mode the default transaction mode in the current
version of SQLite?

Yes.

Post by Alexander J. Kozlovsky
The next is explanation of my point of view.
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,
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.

Post by Alexander J. Kozlovsky
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

[Alexander J. Kozlovsky]

Post by Igor Tandetnik
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

3. At this moment, no one transaction have RESERVED or PENDING or
EXCLUSIVE lock

4. Transaction T2 acquire RESERVED lock, then PENDING, then EXCLUSIVE
and commit transaction.

Result of T1 transaction is lost.


Best regards,
Alexander mailto:kozlovsky-***@public.gmane.org

[Igor Tandetnik]

Alexander J. Kozlovsky

Post by Alexander J. Kozlovsky

Post by Igor Tandetnik
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.

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

[Alexander J. Kozlovsky]

SQLite transactions are always serializable.

Serializable mode requires two-phase locking protocol. In practice
it means all locks (for read and for write) hold until transaction
end. ^^^^^^^^

Deferred transaction drop SHARED lock in middle of transaction
and replace it with RESERVED lock. Hence, it is not two-phase
transaction. AFAIK this behaviour is not allowed for true
serializable transactions.

With correct lock implementation serializable transactions
must not release READ lock in the middle. If two transaction
hold READ locks and then both of them try to acquire WRITE
locks it must lead to deadlock. It is the reason for special
UPDATE or UPGRADE lock mode which means "read, and then
possible write".

In previous mail I describe situation when two transactions
started, acquires SHARED mode and then (one-after-another)
update database and commit successfully. According
documentation (http://sqlite.org/lockingv3.html) this can
take place if first transaction release EXCLUSIVE lock
before second transaction try to acquire RESERVED lock.

Hence, SQLite deferred transactions is not serializable ones.


Best regards,
Alexander mailto:kozlovsky-***@public.gmane.org

[D. Richard Hipp]

Post by Alexander J. Kozlovsky
Hence, SQLite deferred transactions is not serializable ones.

Your understanding is incorrect. SQLite does *not* release
locks in the middle of a transaction - ever. It is always
serializable.

[Alexander J. Kozlovsky]

Yes, I understand now.

Thanks for explanation!



Best regards,
Alexander mailto:kozlovsky-***@public.gmane.org