[sqlite] Database locked in multi process scenario
Sreekumar TP
I have two applications opening the database in WAL mode. The threading
mode is SERIALIZED. Environment is PC/Linux.
Step1: Launch App1 followed by App 2 ( same executables)
Step 2: App1 Prepares a SELECT statement and executes the statement.
Step 3: The statement is not reset and finalized.
Step 4: App1 wait on keyboard input
Step 5 : App2 prepares a statement to update a few records. The statement
is executed, reset and finalized
Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it.
Result: 'database is locked' error is thrown by App 1
App2 should have released the exclusive lock after it has reset and
finalized the statement.Hence App1 should not have encountered SQLITE_BUSY
when writing.
What could be the cause ?
Kind Regards,
Sreekumar
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Richard Hipp
> I have a 'database is locked' issued which can be reproduced as follows.
>
> I have two applications opening the database in WAL mode. The threading
> mode is SERIALIZED. Environment is PC/Linux.
>
>
>
> Step1: Launch App1 followed by App 2 ( same executables)
> Step 2: App1 Prepares a SELECT statement and executes the statement.
> Step 3: The statement is not reset and finalized.
> Step 4: App1 wait on keyboard input
> Step 5 : App2 prepares a statement to update a few records. The statement
> is executed, reset and finalized
> Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it.
>
At this point, App1 is still holding a read transaction open that points to
an image of the database from an earlier point in time. App1 is not
allowed to write to the database because that would cause the database
content to fork. Any database connection must be in a transaction that
shows the very latest content of the database in order for it to write
without forking.
>
> Result: 'database is locked' error is thrown by App 1
>
> App2 should have released the exclusive lock after it has reset and
> finalized the statement.Hence App1 should not have encountered SQLITE_BUSY
> when writing.
>
>
> What could be the cause ?
>
> Kind Regards,
> Sreekumar
> _______________________________________________
> sqlite-users mailing list
> sqlite...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
Sreekumar TP
single process?
Moreover the journal mode is WAL. Hence the writer should be able to append
changes to the WAL file as there are no other write transaction.
Sreekumar
Simon Slavin
On 10 Feb 2012, at 11:47am, Sreekumar TP wrote:
> I have a 'database is locked' issued which can be reproduced as follows.
>
> I have two applications opening the database in WAL mode. The threading
> mode is SERIALIZED. Environment is PC/Linux.
>
>
>
> Step1: Launch App1 followed by App 2 ( same executables)
> Step 2: App1 Prepares a SELECT statement and executes the statement.
> Step 3: The statement is not reset and finalized.
> Step 4: App1 wait on keyboard input
> Step 5 : App2 prepares a statement to update a few records. The statement
> is executed, reset and finalized
> Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it.
Sreekumar, imagine what would happen if two users both got to step 4 at the same point at the same time. There are no circumstances under which either of them could proceed. Whichever of them tried to proceed would invalidate the SELECT that the other one was still in the middle of.
When writing your software, imagine that the database is locked from the 'execute' until you have done the 'finalize'. You want to do the finalize as soon as possible, to unlock the database for other users. You can't wait for a user to type something: their phone might ring or something.
Simon.
Sreekumar TP
well, the 'wait' is a simulation of what happens in the real code.
The error is fatal to the application as it never ever recovers from it
even though the writer has finalized and terminated.
Sreekumar
Simon Slavin
On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
> well, the 'wait' is a simulation of what happens in the real code.
>
> The error is fatal to the application as it never ever recovers from it
> even though the writer has finalized and terminated.
In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes.
If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2 see a BUSY instead.
Sreekumar TP
write of the app2 is scheduled in between.
What you are suggesting is that at any point of time only one process can
have a transaction open in a database?
Sreekumar
Simon Slavin
On 10 Feb 2012, at 1:52pm, Sreekumar TP wrote:
> In the real code, there is no sleep/wait or pause. It so happens that the
> write of the app2 is scheduled in between.
>
> What you are suggesting is that at any point of time only one process can
> have a transaction open in a database?
I understand your question but I don't know enough of the internals of SQLite to answer it. Also, I'm not sure how the time between the first _step() and the step which returns 'no more data' resembles a transaction. I hope someone who knows more than I do will contribute.
Marc L. Allen
When given the initial scenario, I thought that Step 5 would block waiting for App1 to finalize.
Rob Richardson
RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed.
-----Original Message-----
From: sqlite-use...@sqlite.org [mailto:sqlite-use...@sqlite.org] On Behalf Of Sreekumar TP
Sent: Friday, February 10, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database locked in multi process scenario
Richard Hipp
<mla...@outsitenetworks.com>wrote:
> I'm not sure I'm even following how this scenario can happen. Doesn't
> App1 have a Shared lock on the DB? Doesn't App2 require an Exclusive lock
> before it can update something?
>
The OP is running in WAL mode. Different rules apply. In WAL mode, you
can have multiple simultaneous readers concurrently with a single writer.
And the readers all see (possibly different) snapshots of the database from
the point in time where their read transaction was first started.
--
D. Richard Hipp
d...@sqlite.org
Richard Hipp
> Isn't it almost a requirement of a transaction that only one be open at a
> time in a database? If there could be more than one transaction, then
> transaction 1 might start, transaction 2 starts, transaction 1 fails,
> transaction 1 is rolled back, and what happens to transaction 2? One could
> imagine one transaction working an table 1 and a second working on table 2
> which has no connection, but then someone comes along and adds a trigger to
> table 1 that updates table 2. Now we have two simultaneous independent
> transactions working on table 2.
>
SQLite supports only SERIALIZABLE transaction semantics. That means the
end result of the database is as if the various transactions had occurred
in a strictly linear sequence.
But SQLite does allow multiple simultaneous transactions to be in play, as
long as no more than one of them is a write transaction. When a read
transaction begins, it sees a snapshot of the database from the moment in
time when the transaction started. In change that occur to the database
file from other database connections are invisible to that transaction.
The OPs problem is that he has a old read transaction open which is looking
at an historical snapshot of the database, that does not contain the latest
changes to the database. Then he tries to promote that read transaction to
a write transaction. But that is not allowed, because doing so would
"fork" the history of the database file. The result might not be
serializable. Before you can write, you have to first be looking at the
most up-to-date copy of the database.
--
D. Richard Hipp
d...@sqlite.org
Marc L. Allen
So, you're assuming the OP actually started a transaction? Because, otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions?
If there is a BEGIN in there somewhere, we're talking about:
App1:
BEGIN
SELECT
UPDATE
..
App2 BEGIN
UPDATE
...
Right? And you're saying that this causes a problem if App2 gets in between App1's SELECT and UPDATE?
> -----Original Message-----
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Friday, February 10, 2012 9:13 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
>
> On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson <RDRichardson@rad-
Richard Hipp
<mla...@outsitenetworks.com>wrote:
>
> So, you're assuming the OP actually started a transaction? Because,
> otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate
> transactions?
>
The OP said "Step 3: The statement is not reset or finalized". That
doesn't guarantee that the automatic read transaction that was started by
the statement is still open, but it is pretty good hint.
Remember, every statement runs within a transaction. Otherwise, the
information coming out of the SELECT at the beginning might be incompatible
with information that comes out at the end, if another connection modified
the database while the select was running. It is not necessary to
explicitly start a transaction with BEGIN in order to be in a transaction.
If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically
inserted around each SQL statement you run.
Marc L. Allen
Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again?
Igor Tandetnik
> I see. So, the implied commit doesn't occur until you finalize?
Or reset.
> As a result, the subsequent update in step 5 was added to his
> non-finalized select?
The update was attempted within the same transaction.
> Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another
> do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try
> again?
That's one way. The other is for the first connection to start its transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the start.
--
Igor Tandetnik
Richard Hipp
<mla...@outsitenetworks.com>wrote:
> I see. So, the implied commit doesn't occur until you finalize? As a
> result, the subsequent update in step 5 was added to his non-finalized
> select?
>
> Still.. what is the correct way to handle the explicit scenario? I mean,
> having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is
> perfectly reasonable, isn't it? How do you protect from a problem? Detect
> the error, rollback, and try again?
>
One approach is to be prepared to rollback and try again. Or, if you know
that your transaction is going to be reading first and later writing, you
can start with "BEGIN IMMEDIATE" which goes ahead and starts as a write
transaction, guaranteeing that no other process will write ahead of you so
that when you get around to writing yourself, the write won't hit a BUSY.
Marc L. Allen
> -----Original Message-----
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, February 10, 2012 9:43 AM
> To: sqlite...@sqlite.org
> Subject: Re: [sqlite] Database locked in multi process scenario
>
Igor Tandetnik
> How is this different from two threads each with a db connection in a
> single process?
If each thread uses its own separate connection, it should be no different - you would observe the same issue.
> Moreover the journal mode is WAL. Hence the writer should be able to append
> changes to the WAL file as there are no other write transaction.
Your problem is with a transaction stat starts as a reader, and later tries to become a writer. This is only possible if the reader is observing the most recent state of the database, that is, if there were no writes since it started.
Consider:
// initial setup
create table t(count integer);
insert into t values (0);
/* 1 */ select count from t;
/* 2 */ update t set count = count + 10;
/* 1 */ update t set count = count + 1; // (!)
/* 1 */ select count from t; // (!!)
/* 1 */ and /* 2 */ mark operations performed by two separate transactions. Imagine that such a sequence were possible, and the update at (!) succeeded. What value should count have after this update? If it's 11, then a select at (!!) would effectively observe a change written by a different transaction, violating transaction isolation. If it's 1, then an observer in yet third connection could see the count go up, then down - which is surprising as the update statements only ever increment it.
Neither outcome is particularly appealing, so the sequence is prohibited altogether.
There are several ways in which transactions that start as readers and later promote themselves to writers may cause problems. It's best to avoid such situations: if you know that you may need to write eventually, start your transaction with BEGIN IMMEDIATE, then it would be marked as a writer from the outset.
--
Igor Tandetnik
Sreekumar TP
multiprocess/threaded application how can one make assumptions on the order
of updates?
Sreekumar
Marc L. Allen
From my background, I'm used to SQL statements blocking until appropriate locks are acquired. From what I've seen, it looks like sqlite doesn't block, but returns BUSY, is that correct?
If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will it block and wait?
If it returns BUSY, how does sqlite3_exec() handle it?
(Btw.. I know there are books on sqlite, so if you want to point me to one that answers all these questions, or if I'm missing it from the online docs, just let me know. I just haven't found them.)
Thanks
> -----Original Message-----
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Marc L. Allen
> Sent: Friday, February 10, 2012 9:45 AM
> To: General Discussion of SQLite Database
Sreekumar TP
After App1 SELECT
------------------
fcntl -1212610880 7 SETLK WRLCK 124 1 0 0
WAL806F9D8: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 ok
fcntl -1212610880 7 SETLK UNLCK 124 1 0 0
WAL806F9D8: release EXCLUSIVE-READ-LOCK[1] cnt=1
fcntl -1212610880 7 SETLK RDLCK 124 1 0 0
WAL806F9D8: acquire SHARED-READ-LOCK[1] ok
After App2 UPDATE
-----------------
fcntl -1212344640 7 SETLK RDLCK 124 1 0 0
WAL806F570: acquire SHARED-READ-LOCK[1] ok
fcntl -1212344640 7 SETLK WRLCK 120 1 0 0
WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok
WAL806F570: frame write ok
fcntl -1212344640 7 SETLK UNLCK 120 1 0 0
WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1
fcntl -1212344640 7 SETLK UNLCK 124 1 0 0
WAL806F570: release SHARED-READ-LOCK[1]
After App1 SELECT
------------------
fcntl -1212610880 7 SETLK WRLCK 120 1 0 0
WAL806F9D8: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok
fcntl -1212610880 7 SETLK UNLCK 120 1 0 0
WAL806F9D8: release EXCLUSIVE-WRITE-LOCK cnt=1
error @ 225, 1,5,database is locked
After App2 UPDATE ( a second UPDATE )
-------------------------------------
fcntl -1212344640 7 SETLK WRLCK 124 1 0 -1
fcntl-failure-reason: RDLCK 124 1 15295
WAL806F570: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 failed
fcntl -1212344640 7 SETLK WRLCK 125 1 0 0
WAL806F570: acquire EXCLUSIVE-READ-LOCK[2] cnt=1 ok
fcntl -1212344640 7 SETLK UNLCK 125 1 0 0
WAL806F570: release EXCLUSIVE-READ-LOCK[2] cnt=1
fcntl -1212344640 7 SETLK RDLCK 125 1 0 0
WAL806F570: acquire SHARED-READ-LOCK[2] ok
fcntl -1212344640 7 SETLK WRLCK 120 1 0 0
WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok
fcntl -1212344640 7 SETLK UNLCK 120 1 0 0
WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1
fcntl -1212344640 7 SETLK UNLCK 125 1 0 0
WAL806F570: release SHARED-READ-LOCK[2]
Additional Info -
You can also see that acquiring an EXCLUSIVE-READ-LOCK FAILED during the a
second update by App2.
The fcntl error translated means "resource temporarily unavailable"
-
Sreekumar
On Fri, Feb 10, 2012 at 8:31 PM, Marc L. Allen
Simon Slavin
On 10 Feb 2012, at 3:01pm, Marc L. Allen wrote:
> From my background, I'm used to SQL statements blocking until appropriate locks are acquired. From what I've seen, it looks like sqlite doesn't block, but returns BUSY, is that correct?
You can set a timeout. SQLite tries and retries until the timeout expires, then it returns _BUSY.
<http://www.sqlite.org/c3ref/busy_timeout.html>
You can set the timeout to a few milliseconds or a few hours. Both will work.
> If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will it block and wait?
Dependent on whether the database becomes free before the amount of time you set in the timeout.
> If it returns BUSY, how does sqlite3_exec() handle it?
It is the sqlite3_exec() routine which will itself return _BUSY. It's up to your application to handle that well.
Simon.
Sreekumar TP
If you try to rollback, you get the following error -"cannot rollback
savepoint, SQL statments in progress" or if you dont use SAVEPOINT -
"cannot rollback, no transaction is active "
If you start the transaction with BEGIN IMMEDIATE in App1, the writer in
App2 gets the following error " database is locked"
Sreekumar
Simon Slavin
On 10 Feb 2012, at 4:45pm, Sreekumar TP wrote:
> There is no recovery from this situation-
>
> If you try to rollback, you get the following error -"cannot rollback
> savepoint, SQL statments in progress" or if you dont use SAVEPOINT -
> "cannot rollback, no transaction is active "
> If you start the transaction with BEGIN IMMEDIATE in App1, the writer in
> App2 gets the following error " database is locked"
Correct. The database really is locked, since the structure of your application tells SQLite that it is going to make a change soon. If you're not ready to make the change yet, don't lock the database yet.
Simon.
Igor Tandetnik
> The last transaction should always be the final one. In a a
> multiprocess/threaded application how can one make assumptions on the order
> of updates?
There are two updates in my example:
update t set count = count + 1;
update t set count = count + 10;
Do you feel it unreasonable to assume that, after these two statements
are executed successfully, in any order, the value of count should
increase by 11?
If two $100 deposits to your bank account are made by different parties
at approximately the same time, I think you'd be pretty upset if the
account balance didn't increase by precisely $200.
Igor Tandetnik
> There is no recovery from this situation-
>
> If you try to rollback, you get the following error -"cannot rollback
> savepoint, SQL statments in progress" or if you dont use SAVEPOINT -
> "cannot rollback, no transaction is active"
> If you start the transaction with BEGIN IMMEDIATE in App1, the writer in
> App2 gets the following error " database is locked"
Just reset the statement that keeps the transaction open.
If you have more than one write and you want to be able to roll back
reliably if any of them fails, you should start an explicit transaction.
Sreekumar TP
drop one of the transaction and block the database forever (as it is
happening now). Instead, it could be serialized such that two $100
transactions are committed to the db.
On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik <itand...@mvps.org>wrote:
> On 2/10/2012 9:57 AM, Sreekumar TP wrote:
>
>> The last transaction should always be the final one. In a a
>> multiprocess/threaded application how can one make assumptions on the
>> order
>> of updates?
>>
>
> There are two updates in my example:
>
>
> update t set count = count + 1;
> update t set count = count + 10;
>
> Do you feel it unreasonable to assume that, after these two statements are
> executed successfully, in any order, the value of count should increase by
> 11?
>
> If two $100 deposits to your bank account are made by different parties at
> approximately the same time, I think you'd be pretty upset if the account
> balance didn't increase by precisely $200.
>
> --
> Igor Tandetnik
>
> ______________________________**_________________
> sqlite-users mailing list
> sqlite...@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
Simon Slavin
>
>> The last transaction should always be the final one. In a a
>> multiprocess/threaded application how can one make assumptions on the
>> order
>> of updates?
SQL does not have any concept of 'last transaction' or 'final transaction' or 'order of transactions'. Either a transaction is done or it isn't. There is no order for changes to a database done within a transaction. Either they're all done, or none are done. There is no idea that one change is made first, then another, then another.
Similarly, if you specify an order in your SELECT command, that's the order you'll get the rows in. If you don't, the rows may appear in a random order. If you don't specify an ORDER BY clause or perhaps even if you do and your ORDER BY is ambiguous, they could easily appear in a different order every time you do a SELECT.
On 10 Feb 2012, at 5:19pm, Sreekumar TP wrote:
> Though the example of $ is very intuitive, I am not suggesting that we
> drop one of the transaction and block the database forever (as it is
> happening now). Instead, it could be serialized such that two $100
> transactions are committed to the db.
You BEGIN one transaction. In that transaction you make the two changes which balance each other out. You COMMIT the transaction and both changes are made. Or you ROLLBACK the transaction and nether change is made. That's how transactions work.
Simon.
Sreekumar TP
writer lock ? Since both applications use the same WAL file for read and
writes, it shouldnt be a problem , because all changes will be in linear
sequence ?
Sreekumar
Kit
> Though the example of $ is very intuitive, I am not suggesting that we
> drop one of the transaction and block the database forever (as it is
> happening now). Instead, it could be serialized such that two $100
> transactions are committed to the db.
A situation in which I read from the database first and then changes
the data tells me that they are wrong questions. It is such a problem
to insert SELECT into UPDATE or INSERT?
--
Kit
Simon Slavin
On 10 Feb 2012, at 5:29pm, Sreekumar TP wrote:
> Can this situation be handled in sqlite - by upgrading the lock to a
> writer lock ? Since both applications use the same WAL file for read and
> writes, it shouldnt be a problem , because all changes will be in linear
> sequence ?
SQLite handles all of this perfectly. We don't understand why you have a problem. Why are you starting a SELECT command, then waiting to finalize it for a long time ?
Just do your SELECT and complete it. When you're ready to make the changes in your your transaction, do that. There is no need for the SELECT and the changes to be part of the same transaction.
Simon.
Richard Hipp
> There is no recovery from this situation-
>
The recovery from your situation is to reset or finalize the initial query
that is holding the transaction option.
--
D. Richard Hipp
d...@sqlite.org
Simon Slavin
On 10 Feb 2012, at 5:32pm, Kit wrote:
> 2012/2/10 Sreekumar TP <sreeku...@gmail.com>:
>> Though the example of $ is very intuitive, I am not suggesting that we
>> drop one of the transaction and block the database forever (as it is
>> happening now). Instead, it could be serialized such that two $100
>> transactions are committed to the db.
>
> A situation in which I read from the database first and then changes
> the data tells me that they are wrong questions. It is such a problem
> to insert SELECT into UPDATE or INSERT?
Why do you need to do a SELECT at all ? Can you present us with a simple example of your situation ?
Simon.
Kit
> On 10 Feb 2012, at 5:32pm, Kit wrote:
>> A situation in which I read from the database first and then changes
>> the data tells me that they are wrong questions. It is such a problem
>> to insert SELECT into UPDATE or INSERT?
>
> Why do you need to do a SELECT at all ? Can you present us with a simple example of your situation ?
> Simon.
Add value from Alpha to Bravo:
Wrong:
SELECT val AS val1 FROM t1 WHERE name='Alpha';
UPDATE t1 SET val=val+val1 WHERE name='Bravo';
Good:
UPDATE t1 SET val=val+(SELECT val FROM t1 WHERE name='Alpha') WHERE
name='Bravo';
No explicit transaction, no problem.
--
Kit
Simon Slavin
On 10 Feb 2012, at 5:55pm, Kit wrote:
> 2012/2/10 Simon Slavin <sla...@bigfraud.org>:
>> On 10 Feb 2012, at 5:32pm, Kit wrote:
>>> A situation in which I read from the database first and then changes
>>> the data tells me that they are wrong questions. It is such a problem
>>> to insert SELECT into UPDATE or INSERT?
>>
>> Why do you need to do a SELECT at all ? Can you present us with a simple example of your situation ?
>> Simon.
>
> Add value from Alpha to Bravo:
>
> Wrong:
> SELECT val AS val1 FROM t1 WHERE name='Alpha';
> UPDATE t1 SET val=val+val1 WHERE name='Bravo';
>
> Good:
> UPDATE t1 SET val=val+(SELECT val FROM t1 WHERE name='Alpha') WHERE
> name='Bravo';
>
> No explicit transaction, no problem.
Ah, you were suggesting the solution, not asking the question. I misunderstood. Sorry.
Simon.
Igor Tandetnik
> Can this situation be handled in sqlite - by upgrading the lock to a
> writer lock ? Since both applications use the same WAL file for read and
> writes, it shouldnt be a problem , because all changes will be in linear
> sequence ?
Consider again:
[1] BEGIN;
[1] SELECT balance from Accounts; (1)
[2] BEGIN;
[2] SELECT balance from Accounts; (2)
[2] UPDATE Accounts SET balance = balance + 100;
[2] SELECT balance from Accounts; (3)
[2] COMMIT;
[1] UPDATE Accounts SET balance = balance + 100;
[1] SELECT balance from Accounts; (4)
[1] COMMIT;
[3] SELECT balance from Accounts; (5)
Statements are shown in the order they are submitted to SQLite. Numbers
in square brackets indicate individual connections.
Let's suppose SQLite does everything the way you want (it's not quite
clear what it is you want exactly, so that's what I'm trying to
establish). I assume you expect this sequence of statements to succeed.
In this ideal world, what value of Accounts.balance should be observed
at points (1), (2), (3), (4) and (5), in your opinion?
--
Igor Tandetnik
Marc L. Allen
> -----Original Message-----
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, February 10, 2012 2:36 PM
> To: sqlite...@sqlite.org
> Subject: Re: [sqlite] Database locked in multi process scenario
>
> > Can this situation be handled in sqlite - by upgrading the lock to a
> > writer lock ? Since both applications use the same WAL file for read
> > and writes, it shouldnt be a problem , because all changes will be in
> > linear sequence ?
>
> Consider again:
>
> [1] BEGIN;
> [1] SELECT balance from Accounts; (1)
>
> [2] BEGIN;
> [2] SELECT balance from Accounts; (2)
> [2] UPDATE Accounts SET balance = balance + 100;
> [2] SELECT balance from Accounts; (3)
> [2] COMMIT;
>
> [1] UPDATE Accounts SET balance = balance + 100; [1] SELECT balance
> from Accounts; (4) [1] COMMIT;
>
> [3] SELECT balance from Accounts; (5)
>
> Statements are shown in the order they are submitted to SQLite. Numbers
> in square brackets indicate individual connections.
>
> Let's suppose SQLite does everything the way you want (it's not quite
> clear what it is you want exactly, so that's what I'm trying to
> establish). I assume you expect this sequence of statements to succeed.
> In this ideal world, what value of Accounts.balance should be observed
> at points (1), (2), (3), (4) and (5), in your opinion?
Well.. in MSSQL that would work the way you think he expects. That is, if the initial balance is $100
(1) 100
(2) 100
(3) 200
(4) 300
(5) 300
MSSQL in its default serialization mode does not guarantee repeatable reads within a transaction. But, it provides locking hints to help enforce it when required. I'm guessing that sqlite does guarantee repeatable reads?
Marc
Igor Tandetnik
> MSSQL in its default serialization mode does not guarantee repeatable
> reads within a transaction. But, it provides locking hints to help
> enforce it when required. I'm guessing that sqlite does guarantee
> repeatable reads?
SQLite implements only one transaction isolation level - serializable
(except in shared cache mode). In regular journal mode, this is achieved
by holding locks. In WAL mode, this is effectively achieved via snapshot
isolation (http://en.wikipedia.org/wiki/Snapshot_isolation)
--
Igor Tandetnik