Re: [sqlite] Database locked problem

[Simon Slavin]

On 30 Sep 2018, at 3:14pm, Lars Frederiksen  wrote:

> In fact I had the same error some time ago where the solution was to put my 
> database in a C-drive root folder. But that is where the db is right now!!

It is very unusual under Windows to have enough privileges to change files in 
C:\ .  Any program with those powers could mess up the computer with malware.

I don't think your problems are related to SQLite.  I think you need to find a 
folder your program can definitely write to.  You might be able to use some 
simple function in Delphi to write a simple file and see whether this problem 
occurs with that:



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked problem on Windows 7

[Yngve N. Pettersen]

On Tue, 31 Jul 2018 18:00:31 +0200, Richard Hipp  wrote:


On 7/31/18, Yngve N. Pettersen  wrote:

I sent the email quoted below to the list, but
unfortunately, as far as I can tell, I have so far received no response.



I think that means that nobody has an answer.  I don't have any idea
why your system would work well on Win10 but not on Win7.  SQLite
should work the same on both.


I may actually just have found the root cause of the problem.

The distributed executable is built using pyinstaller for Python 3.6, and  
was built on Windows 10.  I started to wonder if this was relevant to the  
problem, and if building the executable on Windows 7 Pro would help fix  
the problem.


One of the main "irritants" of this system is that it is warning about  
some Windows API DLLs that are missing, and is required by Python 3.6. I  
have previously ignored these warnings.


A bit of digging indicates that these warnings may indeed be relevant to  
the problem. The DLLs are handled invisibly by Windows 10, but exists on  
pre-Win10 systems. See  
 and  
  
. One either have to build on Win7 to get these DLLs included, or take  
some advanced actions when building the executable.


I copied the whole source over to a Win 7 machine, and built it there, and  
had no problems running the full test without encountering any lock  
issues, the test was also slightly faster than before, even without the  
hacks I had used earlier.


IOW: AFAICT If a project using Python Sqlite3 needs to run on Windows 7  
and the executable is generated with Pyinstaller, then the easiest way to  
avoid issues is to generate the executable on a Windows 7 system.


This might conceivably apply to other kinds of projects, too, depending on  
the build environment.


--
Sincerely,
Yngve N. Pettersen
Vivaldi Technologies AS
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked problem on Windows 7

[Richard Hipp]

On 7/31/18, Yngve N. Pettersen  wrote:
> I sent the email quoted below to the list, but
> unfortunately, as far as I can tell, I have so far received no response.
>

I think that means that nobody has an answer.  I don't have any idea
why your system would work well on Win10 but not on Win7.  SQLite
should work the same on both.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked problem on Windows 7

[Yngve N. Pettersen]

Hello all,

About four weeks ago, I sent the email quoted below to the list, but  
unfortunately, as far as I can tell, I have so far received no response.


TL;DR: Concurrent attempts to exclusively lock the database tend to fail  
on Windows 7 Pro, there is no similar problem on Windows 10.


The only real updates from what I wrote earlier is that 1) a small  
reorganization of the insertion queries and a single global mutex lock got  
the run time down to about the same as the old system, which is still  
about double the time of Window 10. 2) Using more than one mutex and  
relying on the sqlite exclusive lock to arbitrate between them  
significantly increased the runtime (triple or more).



On Thu, 05 Jul 2018 20:33:44 +0200, Yngve N. Pettersen   
wrote:



Hello all,

I am working on a project involving the Python SQLite3 API for SQLite (a
compile cache system), and I have started running into "database locked"
problems on the Windows 7 Pro machines some of the instances will run on;
the Windows 10 instances works without any problems.

The database is configured with WAL journaling, and when deployed will
have up to 42 active connection at a time. There are three tables, one
containing a blob with information about a source file and a time stamp
(the timestamp is updated each time the entry is used, the blob may be
updated), one with a blob of binary data, and the third have the time
stamp for the corresponding entry in the second table (updated each time
the blob entry is used). The Python SQLite3 connection is started with a
timeout of 100 seconds.

The lock problem does not appear when seeding the database, but that
process involves compiling the source first, so database updates should
be less frequent.

The problem occurs when the data is (mostly) only being pulled out of the
database for reuse, followed by an update of the time stamp the rows  
used (to be

used when removing old entries).

Using a locally built DLL with some printfs I found that it looks like  
the

problem occurs when winLock/winLockFile is called from sqlite3WalClose to
obtain an exclusive lock; it seems to fail in the Exclusive lock part of
the function. The reported windows error code is 33.

For reference, the folder where the database is stored, is excluded from
AV scans, the disk drives are all local SSDs. The size of the DB is
currently about 4GB. The SQLite DLL version from Python 3.6 (can't use
3.7) is 3.21 (no change with 3.24).

I have found a few "workarounds", most of them not remotely satisfactory:

- Increase busy_timeout to at least 40 seconds, wal_autocheckpoint to
3000+ seems to work, although I have had failures in this scenario, too.
The downside is that this takes (at least) as long as seeding the
database, and 5-6 times as long as the system it is supposed to replace,
and 10(!) times as long as the corresponding tests on Windows 10 (and 7
times as long as on my own Win10 machine with half the cores).

- Use defaults for busy_timeout, wal_autocheckpoint, and use a Windows
mutex to lock access to the database during write operation. This *is*
much faster than the previous example, but that is the best that can be
said for it. It is still 10-20% slower than the old system, takes twice  
as

long as the Win 10 test (and 20-40% longer than tests on my own machine,
with half the cores). Trying to use multiple mutexes only took longer.

- I tried resuming the operation after a lock, that was also slower than
both the old system and the Win 10 system, and it also ran into trouble
when a repeated insert operation broke the uniqeness constraint (the
transaction had completed and been committed before the database locked
error occurred).

- Reducing number of parallel jobs to 20 on the 32 (logical) core machine
worked (24 failed almost at the end), but the reuse case still took close
to 4 times as long as the old system (and 2/3 of the time used for a
normal seeding using). OTOH, using 20 jobs on a 40 core Win 10 machine
took only twice as long as using all cores (effectively what the old
system used).

- I also tried to rebuild the database without WAL. Seeding the database
took 3 times longer than it did with WAL, and the second stage (reuse)
seems to take as long (stopped it when it had taken longer than a normal
seed, when it was just 25% finished).

(And yes, upgrading to Win 10 may be a possibility, although at least two
of the machines was not able to use Win 10 last time I tried.)

I don't know if this is a problem caused by a problem in SQLite, or if it
as limitation in Windows 7.

That halving the number of jobs takes 4 times as long on Win 7 as the old
job (which indicates that a all cores would still require double the time
of the old system), while the Win 10 version was running just doubling  
(as

expected) the its original time, which was half of the old system's time,
seems to indicate that there is a performance issue with SQLite on  
Windows 7
(and I don't see similar issues with other 

Re: [sqlite] database locked on select

[Torsten Curdt]

I have to query an external resource for each row.
Unfortunately nothing I can do in a single update query.
Would mean keeping a lot of data manually in memory.

On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
abrozynieprzelozy314...@gmail.com> wrote:

> BTW why not to update all rows by single update query?
>
> 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > I am doing a select, then iterate through the resultset and on each row
> > call update on that row.
> > I am using the golang driver and ran into the issue that on the update
> the
> > database is still locked from the select.
> >
> >   https://github.com/mattn/go-sqlite3/issues/569
> >
> > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and
> IIUC
> > these types of updates should be possible since version 3.3.7 though -
> and
> > I am using 3.19.3.
> >
> > Any suggestion on how to track down why the updates fail?
> >
> > cheers,
> > Torsten
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Keith Medcalf]

or 1 thread...or 6000 threads...makes no difference whatever.  Unless you 
changed the default from SERIALIZED to something that does not apply.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Deon Brewis
>Sent: Sunday, 27 May, 2018 16:50
>To: SQLite mailing list
>Subject: Re: [sqlite] database locked on select
>
>By one connection doing SELECT and UPDATE, do you mean multi-threaded
>mode and using the connection from 2 threads?
>
>- Deon
>
>-Original Message-
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Simon Slavin
>Sent: Sunday, May 27, 2018 3:39 PM
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] database locked on select
>
>On 27 May 2018, at 7:30pm, Torsten Curdt <tcu...@vafer.org> wrote:
>
>> I am doing a select, then iterate through the resultset and on each
>> row call update on that row.
>> I am using the golang driver and ran into the issue that on the
>update
>> the database is still locked from the select.
>
>Are you usihg one connection to do both the SELECT and the UPDATE ?
>Or are you using two different connections, one for the SELECT and
>one for the UPDATE ?
>
>The second method will not work.  The SELECT connection has the
>database locked until you tell your interface you have finished with
>the SELECT.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Abroży Nieprzełoży]

BTW why not to update all rows by single update query?

2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> I am doing a select, then iterate through the resultset and on each row
> call update on that row.
> I am using the golang driver and ran into the issue that on the update the
> database is still locked from the select.
>
>   https://github.com/mattn/go-sqlite3/issues/569
>
> I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and IIUC
> these types of updates should be possible since version 3.3.7 though - and
> I am using 3.19.3.
>
> Any suggestion on how to track down why the updates fail?
>
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Torsten Curdt]

There is no multi threading. Just a single thread and only one
connection/handle.
While iterating through the resultset I am also trying to execute an update
for every row. Along the lines of:

  resultset = db.exec(`select`)
  foreach row in resultset {
db.exec(`update`)
  }

I don't want to read the whole resultset into memory.

After a lot of searching and trial and error I found that

  PRAGMA journal_mode=WAL

seems to do the trick.
I don't fully understand why though.

cheers,
Torsten
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Simon Slavin]

On 27 May 2018, at 11:49pm, Deon Brewis  wrote:

> By one connection doing SELECT and UPDATE, do you mean multi-threaded mode 
> and using the connection from 2 threads?

A connection cannot lock the database against itself.  If you are doing two 
operations with one connection, there can be no SQLite lock.

However, there may be another mechanism which is doing another kind of locking. 
 And it may be something that the golang driver is doing, rather than something 
built into SQLite.  I hope someone else knows.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Deon Brewis]

By one connection doing SELECT and UPDATE, do you mean multi-threaded mode and 
using the connection from 2 threads?

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Simon Slavin
Sent: Sunday, May 27, 2018 3:39 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] database locked on select

On 27 May 2018, at 7:30pm, Torsten Curdt <tcu...@vafer.org> wrote:

> I am doing a select, then iterate through the resultset and on each 
> row call update on that row.
> I am using the golang driver and ran into the issue that on the update 
> the database is still locked from the select.

Are you usihg one connection to do both the SELECT and the UPDATE ?  Or are you 
using two different connections, one for the SELECT and one for the UPDATE ?

The second method will not work.  The SELECT connection has the database locked 
until you tell your interface you have finished with the SELECT.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Simon Slavin]

On 27 May 2018, at 7:30pm, Torsten Curdt  wrote:

> I am doing a select, then iterate through the resultset and on each row
> call update on that row.
> I am using the golang driver and ran into the issue that on the update the
> database is still locked from the select.

Are you usihg one connection to do both the SELECT and the UPDATE ?  Or are you 
using two different connections, one for the SELECT and one for the UPDATE ?

The second method will not work.  The SELECT connection has the database locked 
until you tell your interface you have finished with the SELECT.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked in PHP

[Hick Gunter]

Maybe you can use the (linux, c) code I posted recently to determine which 
process/thread is locking the database file.

-Ursprüngliche Nachricht-
Von: Lev [mailto:leventel...@gmail.com]
Gesendet: Sonntag, 25. Jänner 2015 01:36
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] database locked in PHP

On Sat, 24 Jan 2015 20:59:22 +
Simon Slavin <slavins-drl2ohjjk6tg9huczpv...@public.gmane.org> wrote:

> and set it to 6 (60 seconds) or so.

Okay, I try that, but I still don't understand how can a single threaded 
application get a locked error.

Levente

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked in PHP

[Dan Kennedy]

On 01/25/2015 07:35 AM, Lev wrote:

On Sat, 24 Jan 2015 20:59:22 +
Simon Slavin  wrote:


and set it to 6 (60 seconds) or so.

Okay, I try that, but I still don't understand how can a single threaded
application get a locked error.


Was the error message "database is locked" or "database table is locked"?

When you say "single threaded", are you also implying "uses a single 
connection"?


Is the statement that is hitting the SQLITE_LOCKED a DROP TABLE or DROP 
INDEX statement? If so, do you also have active statements associated 
with the same connection?


Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked in PHP

[Lev]

On Sat, 24 Jan 2015 20:59:22 +
Simon Slavin  wrote:

> and set it to 6 (60 seconds) or so.

Okay, I try that, but I still don't understand how can a single threaded
application get a locked error.

Levente

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked in PHP

[Simon Slavin]

On 24 Jan 2015, at 8:13pm, Lev  wrote:

> I sometimes get a database locked error when I access the database by
> calling the execute() call. This is on PHP.

Have you set a timeout ?  Immediately after opening the connection use



and set it to 6 (60 seconds) or so.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked after read

[Pavel Ivanov]

> I have found that when using SQLite under Windows if you execute an update
> statement and then a select statement, the database is locked until the
> select statement is finished.  How can I stop this from happening?

Note that you don't have to execute UPDATE before the SELECT to
reproduce that. SELECT places shared lock on the database. UPDATE
cannot proceed while any shared locks are active on the database. To
avoid such situation you can switch your database to the WAL journal
mode (http://www.sqlite.org/wal.html).


Pavel


On Sat, Dec 1, 2012 at 2:02 PM, Scott Ferrett
 wrote:
> I have found that when using SQLite under Windows if you execute an update
> statement and then a select statement, the database is locked until the
> select statement is finished.  How can I stop this from happening?
>
> Here is a simple test program that simulates this situation.  If you run the
> program you will get the message "Update executed without problems".
> Without closing this message you run the program again you will get "Table
> is locked??".
>
> void DisplayMessage(_TCHAR* message)
> {
>   MessageBox(NULL, message, "Test Lock", 0);
> }
>
> void Check(int status, char* message)
> {
>   if (status != 0 && status != SQLITE_ROW && status != SQLITE_DONE)
> DisplayMessage(message);
> }
> int _tmain(int argc, _TCHAR* argv[])
> {
>   sqlite3* connection;
>   bool needCreate = sqlite3_open_v2("test.sqlite", ,
> SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK;
>   if (!needCreate || sqlite3_open_v2("test.sqlite", ,
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)
>   {
> if (needCreate)
> {
>   Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE (id LONG, data
> CHAR(30))", NULL, NULL, NULL), "Create Table");
>   Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE2 (id LONG, data
> CHAR(30))", NULL, NULL, NULL), "Create Table 2");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (1, 'A')",
> NULL, NULL, NULL), "Insert 1");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (2, 'B')",
> NULL, NULL, NULL), "Insert 2");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (1,
> 'A')", NULL, NULL, NULL), "Insert 1");
>   Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (2,
> 'B')", NULL, NULL, NULL), "Insert 2");
> }
> sqlite3_stmt* statement1;
> Check(sqlite3_prepare_v2(connection, "UPDATE TESTFILE SET data='A' WHERE
> id=1", -1, , NULL), "Update");
> int status = sqlite3_step(statement1);
> sqlite3_finalize(statement1);
> sqlite3_stmt* statement2;
> Check(sqlite3_prepare_v2(connection, "SELECT ID,data FROM TESTFILE2",
> -1, , NULL), "SELECT");
> Check(sqlite3_step(statement2), "stepping Select");
> if (status == SQLITE_DONE)
>   DisplayMessage("Update executed without problems"); // This simulates
> a select statement that takes a long time to execute
> else if (status == SQLITE_BUSY)
>   DisplayMessage("Table is locked??");
> else
>   DisplayMessage("There was a problem");
> sqlite3_finalize(statement2);
> sqlite3_close(connection);
>   }
>   return 0;
> }
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

On 2/10/2012 2:57 PM, Marc L. Allen wrote:

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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, February 10, 2012 2:36 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> On 2/10/2012 12:29 PM, 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 ?
> 
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

On 2/10/2012 12:29 PM, 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 ?


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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 5:55pm, Kit wrote:

> 2012/2/10 Simon Slavin :
>> 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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Kit]

2012/2/10 Simon Slavin :
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

On 10 Feb 2012, at 5:32pm, Kit wrote:

> 2012/2/10 Sreekumar TP :
>>  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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 11:45 AM, Sreekumar TP wrote:

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


>
> 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
>
> On Fri, Feb 10, 2012 at 8:13 PM, Igor Tandetnik 
> wrote:
>
> > Marc L. Allen  wrote:
> > > 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
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Kit]

2012/2/10 Sreekumar TP :
>  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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

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 ?

Sreekumar


On Fri, Feb 10, 2012 at 10:49 PM, 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.
>
>
>
> On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik 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-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Simon Slavin]

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

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

 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.


On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik 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-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

On 2/10/2012 11:45 AM, 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"


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.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

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-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

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"

Sreekumar

On Fri, Feb 10, 2012 at 8:13 PM, Igor Tandetnik  wrote:

> Marc L. Allen  wrote:
> > 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
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[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.



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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

I took traces of the lock/unlock pattern -


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
<mlal...@outsitenetworks.com>wrote:

> One last question or series (I hope)...
>
> 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-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Marc L. Allen
> > Sent: Friday, February 10, 2012 9:45 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > Thanks so much for clarifying that.  I was unaware of the BEGIN
> > IMMEDIATE.  Sorry.. new to sqlite, used to MySQL and MSSQL.
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of Igor Tandetnik
> > > Sent: Friday, February 10, 2012 9:43 AM
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] Database locked in multi process scenario
> > >
> > > Marc L. Allen <mlal...@outsitenetworks.com> wrote:
> > > > 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
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

One last question or series (I hope)...

>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-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Marc L. Allen
> Sent: Friday, February 10, 2012 9:45 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> Thanks so much for clarifying that.  I was unaware of the BEGIN
> IMMEDIATE.  Sorry.. new to sqlite, used to MySQL and MSSQL.
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Igor Tandetnik
> > Sent: Friday, February 10, 2012 9:43 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > Marc L. Allen <mlal...@outsitenetworks.com> wrote:
> > > 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
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

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?


Sreekumar


On Fri, Feb 10, 2012 at 8:16 PM, Igor Tandetnik  wrote:

> Sreekumar TP  wrote:
> > 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
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

Sreekumar TP  wrote:
> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

Thanks so much for clarifying that.  I was unaware of the BEGIN IMMEDIATE.  
Sorry.. new to sqlite, used to MySQL and MSSQL.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Friday, February 10, 2012 9:43 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> Marc L. Allen <mlal...@outsitenetworks.com> wrote:
> > 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
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:32 AM, Marc L. Allen
<mlal...@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.



>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: Friday, February 10, 2012 9:28 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> > On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen
> > <mlal...@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.
> >
> >
> > >
> > > If there is a BEGIN in there somewhere, we're talking about:
> > >
> > > App1:
> > >BEGIN
> > >SELECT
> > >UPDATE
> > >..
> > >
> > > App2BEGIN
> > >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-users-boun...@sqlite.org [mailto:sqlite-users-
> > > > boun...@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-
> > > > con.com>wrote:
> > > >
> > > > > 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 tr

Re: [sqlite] Database locked in multi process scenario

[Igor Tandetnik]

Marc L. Allen  wrote:
> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

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?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Friday, February 10, 2012 9:28 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen
> <mlal...@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.
> 
> 
> >
> > If there is a BEGIN in there somewhere, we're talking about:
> >
> > App1:
> >BEGIN
> >SELECT
> >UPDATE
> >..
> >
> > App2BEGIN
> >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-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@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-
> > > con.com>wrote:
> > >
> > > > 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.
> > >
> > >
> > >
> > > >
> > > > RobR, who has been struggling for months with a program t

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen
<mlal...@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.


>
> If there is a BEGIN in there somewhere, we're talking about:
>
> App1:
>BEGIN
>SELECT
>UPDATE
>..
>
> App2BEGIN
>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-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@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-
> > con.com>wrote:
> >
> > > 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.
> >
> >
> >
> > >
> > > 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-users-boun...@sqlite.org [mailto:
> > > sqlite-users-boun...@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
> > >
> > > 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?
> > >
> > >
> > > Sreekumar
> > > On Feb 10, 2012 7:12 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:
> > >
> > > >
> > > > On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
> > > >
> > > > >

Re: [sqlite] Database locked in multi process scenario

[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
..

App2BEGIN
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-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@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-
> con.com>wrote:
> 
> > 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.
> 
> 
> 
> >
> > 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-users-boun...@sqlite.org [mailto:
> > sqlite-users-boun...@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
> >
> > 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?
> >
> >
> > Sreekumar
> > On Feb 10, 2012 7:12 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:
> >
> > >
> > > 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.
> > >
> > > Simon.
> > > 

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson <rdrichard...@rad-con.com>wrote:

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



>
> 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-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@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
>
> 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?
>
>
> Sreekumar
> On Feb 10, 2012 7:12 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:
>
> >
> > 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.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 9:01 AM, Marc L. Allen
<mlal...@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.



>
> When given the initial scenario, I thought that Step 5 would block waiting
> for App1 to finalize.
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Simon Slavin
> > Sent: Friday, February 10, 2012 8:55 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Database locked in multi process scenario
> >
> >
> > 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.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Rob Richardson]

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.  

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-users-boun...@sqlite.org [mailto:sqlite-users-boun...@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

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?


Sreekumar
On Feb 10, 2012 7:12 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:

>
> 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.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Marc L. Allen]

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?

When given the initial scenario, I thought that Step 5 would block waiting for 
App1 to finalize.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Friday, February 10, 2012 8:55 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked in multi process scenario
> 
> 
> 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.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[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.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

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?


Sreekumar
On Feb 10, 2012 7:12 PM, "Simon Slavin"  wrote:

>
> 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.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[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.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

Hi Simon,

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
On Feb 10, 2012 6:57 PM, "Simon Slavin"  wrote:

>
> 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.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Sreekumar TP]

How is this different from two threads each with a db connection in a
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
On Feb 10, 2012 6:22 PM, "Richard Hipp"  wrote:

> On Fri, Feb 10, 2012 at 6:47 AM, 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.
> >
>
> 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-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Richard Hipp]

On Fri, Feb 10, 2012 at 6:47 AM, 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.
>

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-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked error, while deleting

[D. Richard Hipp]

On Jun 10, 2008, at 10:18 AM, Shailesh Birari wrote:

> Richard,
> Just to go one step ahead, If there are independent connections, do  
> you
> mean that there can exist multiple transactions at the same time? I
> think not since the first transaction (write) will exclusively lock  
> the
> database and the second transaction will get a busy error. Correct  
> me if
> I am wrong. Will the behaviour differ in case of shared cache enabled
> and disabled?
>

In shared cache mode, you can do "PRAGMA read_uncommited=ON" and then  
the reader will be able to see uncommitted data from the writer.  And  
you won't ever get SQLITE_BUSY because the reader is unable to read  
the schema on account of a busy write transaction.

An easier thing for you to do might be to use a single database  
connection.  That way the reader will certainly never block waiting on  
the writer since they will be serialized.

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked error, while deleting

[Shailesh Birari]

Richard, 
Just to go one step ahead, If there are independent connections, do you
mean that there can exist multiple transactions at the same time? I
think not since the first transaction (write) will exclusively lock the
database and the second transaction will get a busy error. Correct me if
I am wrong. Will the behaviour differ in case of shared cache enabled
and disabled?

-Shailesh. 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
> Sent: Tuesday, June 10, 2008 5:28 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database locked error, while deleting
> 
> 
> On Jun 10, 2008, at 7:36 AM, Sabyasachi Ruj wrote:
> 
> > Hi,
> >
> > I have a very big table with around 40,00, 000 rows. 4 columns. 220 
> > MB.
> >
> > Now, I have two threads:-
> > Thread1: Is deleting 7,00, 000 rows from the table.
> > Thread2: Is doing SELECT on the same table.
> >
> > Now the problem is sometimes the sqlite3_prepare for the 
> SELECT query 
> > is failing with SQLITE_BUSY error.
> >
> > My questions: -
> > 1. What is the best way to handle this error, and continue working 
> > normally?
> > 2. Is there any documentation in sqlite3.org, which discuses the 
> > locking mechanism for DELETEs? Exacly in what phase of 
> DELETE sqlite 
> > creates the exclusive lock, so, the SELECT is failing?
> >
> 
> 
> Are the two threads using the same database connection, or 
> are they making separate and independent calls to sqlite3_open()?
> 
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked error, while deleting

[Sabyasachi Ruj]

Our PRAGMA cache_size = 4000.
Is there any recommended way to calculate the cache size needed?


On Tue, Jun 10, 2008 at 5:29 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> "Sabyasachi Ruj" <[EMAIL PROTECTED]>
> wrote in message
> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> > Now, I have two threads:-
> > Thread1: Is deleting 7,00, 000 rows from the table.
> > Thread2: Is doing SELECT on the same table.
> >
> > Now the problem is sometimes the sqlite3_prepare for the SELECT query
> > is failing with SQLITE_BUSY error.
>
> Are you sure it's the prepare that fails, and not step? Only the very
> first prepare after opening the connection may fail this way, because
> SQLite needs to read database schema at this point.
>
> > 1. What is the best way to handle this error, and continue working
> > normally?
>
> Retry until you manage to go through. Or, prepare all the necessary
> statements up front, before commencing any database work. Then prepare
> won't fail (of course, step still may).
>
> > 2. Is there any documentation in sqlite3.org, which discuses the
> > locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite
> > creates the exclusive lock, so, the SELECT is failing?
>
> http://sqlite.org/lockingv3.html
>
> EXCLUSIVE lock is obtained whenever SQLite needs to write to the
> database file. This may happen when the transaction is committed, or
> when the number of changes in a still-open transaction becomes so large
> that it no longer fits in a memory cache and has to be spilled to disk.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Sabyasachi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked error, while deleting

[Sabyasachi Ruj]

We are using two different connections to sqlite for these two different
threads.
So, we are not sharing the connection in SELECT and the DELETE thread.

On Tue, Jun 10, 2008 at 5:28 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On Jun 10, 2008, at 7:36 AM, Sabyasachi Ruj wrote:
>
> > Hi,
> >
> > I have a very big table with around 40,00, 000 rows. 4 columns. 220
> > MB.
> >
> > Now, I have two threads:-
> > Thread1: Is deleting 7,00, 000 rows from the table.
> > Thread2: Is doing SELECT on the same table.
> >
> > Now the problem is sometimes the sqlite3_prepare for the SELECT
> > query is
> > failing with SQLITE_BUSY error.
> >
> > My questions: -
> > 1. What is the best way to handle this error, and continue working
> > normally?
> > 2. Is there any documentation in sqlite3.org, which discuses the
> > locking
> > mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates
> > the
> > exclusive lock, so, the SELECT is failing?
> >
>
>
> Are the two threads using the same database connection, or are they
> making separate and independent calls to sqlite3_open()?
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Sabyasachi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked error, while deleting

[Igor Tandetnik]

"Sabyasachi Ruj" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> Now, I have two threads:-
> Thread1: Is deleting 7,00, 000 rows from the table.
> Thread2: Is doing SELECT on the same table.
>
> Now the problem is sometimes the sqlite3_prepare for the SELECT query
> is failing with SQLITE_BUSY error.

Are you sure it's the prepare that fails, and not step? Only the very 
first prepare after opening the connection may fail this way, because 
SQLite needs to read database schema at this point.

> 1. What is the best way to handle this error, and continue working
> normally?

Retry until you manage to go through. Or, prepare all the necessary 
statements up front, before commencing any database work. Then prepare 
won't fail (of course, step still may).

> 2. Is there any documentation in sqlite3.org, which discuses the
> locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite
> creates the exclusive lock, so, the SELECT is failing?

http://sqlite.org/lockingv3.html

EXCLUSIVE lock is obtained whenever SQLite needs to write to the 
database file. This may happen when the transaction is committed, or 
when the number of changes in a still-open transaction becomes so large 
that it no longer fits in a memory cache and has to be spilled to disk.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked error, while deleting

[D. Richard Hipp]

On Jun 10, 2008, at 7:50 AM, Alex Katebi wrote:

> There can only be one prepare per table at a time. The first prepare  
> has to
> be finalized before another one. Table is locked by the first  
> prepare until
> finalized.
>

Actually, there is no limit on the number of prepared statements on a  
single table at a single time.  The above is not correct.

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked error, while deleting

[Alex Katebi]

There can only be one prepare per table at a time. The first prepare has to
be finalized before another one. Table is locked by the first prepare until
finalized.

On Tue, Jun 10, 2008 at 7:36 AM, Sabyasachi Ruj <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB.
>
> Now, I have two threads:-
> Thread1: Is deleting 7,00, 000 rows from the table.
> Thread2: Is doing SELECT on the same table.
>
> Now the problem is sometimes the sqlite3_prepare for the SELECT query is
> failing with SQLITE_BUSY error.
>
> My questions: -
> 1. What is the best way to handle this error, and continue working
> normally?
> 2. Is there any documentation in sqlite3.org, which discuses the locking
> mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the
> exclusive lock, so, the SELECT is failing?
>
>
> --
> Sabyasachi
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked version 3.3.17

[Ken]

Resolved the issue. My issue on the app side. 
 
 I think the newer version is much faster. So much so that it changed timing 
characteristics and reveald a logic bug in my code.
 
 Appologies,
 Ken
  
 
 
  

Ken <[EMAIL PROTECTED]> wrote: Hi all,
 
 Think I may have hit some form of a bug in 3.3.17:
 At an insert statement I'm getting a rc=[5] msg=[database is locked] 
 
 When I recompile the sqlite using version 3.3.13 and re-run the appliation. No 
problems.
 
 Sorry this is pretty vague.. I'll try digging in more and finding more details 
to reproduce a simple test case.
 
 Ken
 
 

Re: [sqlite] Database Locked

[John Stanton]

You have to find what is locking the database and unlock it.

Lloyd wrote:

I have tracked down the problem and found th following.

I have a select statement which stores the result set in a pointer
variable. If the result set of select statement is empty, the program
executes, otherwise it gives a Database Locked error.   


How can I solve this problem ?

Thanks again,
  Lloyd.



On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote:


Hi,
 I have opened a database, created tables and inserted data to it in a
single transaction. Then I have closed the data base. Then I reopened
the database and tried to update the table entries. But it throws an
exception called Database Locked. Where can be the mistake? I am using
wxSqlite3 wrapper class. All these are performed from a single program. 


Thanks,
 Lloyd.


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database Locked

[Lloyd]

Thanks Clay Dowling,

Yes there is a function for fanalize function. Now the problem is
solved.


On Tue, 2006-11-14 at 08:02 -0500, Clay Dowling wrote:
> Lloyd,
> 
> You need at some point to call the sqlite3_finalize function.  I don't use
> wxSqlite, but if it's not handled by the destructor of the recordset then
> there should be a method, possibly called finalize or close, that needs to
> be called.  I recommend consulting the documentation for wxSQLite since
> most of the wxWidgets components are pretty well documented.
> 
> Clay Dowling
> 
> Lloyd said:
> > I have tracked down the problem and found th following.
> >
> > I have a select statement which stores the result set in a pointer
> > variable. If the result set of select statement is empty, the program
> > executes, otherwise it gives a Database Locked error.
> >
> > How can I solve this problem ?
> >
> > Thanks again,
> >   Lloyd.
> >
> >
> >
> > On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote:
> >> Hi,
> >>   I have opened a database, created tables and inserted data to it in a
> >> single transaction. Then I have closed the data base. Then I reopened
> >> the database and tried to update the table entries. But it throws an
> >> exception called Database Locked. Where can be the mistake? I am using
> >> wxSqlite3 wrapper class. All these are performed from a single program.
> >>
> >> Thanks,
> >>   Lloyd.
> >>
> >>
> >> __
> >> Scanned and protected by Email scanner
> >>
> >> -
> >> To unsubscribe, send email to [EMAIL PROTECTED]
> >> -
> >
> >
> > __
> > Scanned and protected by Email scanner
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
> 
> 


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database Locked

[Clay Dowling]

Lloyd,

You need at some point to call the sqlite3_finalize function.  I don't use
wxSqlite, but if it's not handled by the destructor of the recordset then
there should be a method, possibly called finalize or close, that needs to
be called.  I recommend consulting the documentation for wxSQLite since
most of the wxWidgets components are pretty well documented.

Clay Dowling

Lloyd said:
> I have tracked down the problem and found th following.
>
> I have a select statement which stores the result set in a pointer
> variable. If the result set of select statement is empty, the program
> executes, otherwise it gives a Database Locked error.
>
> How can I solve this problem ?
>
> Thanks again,
>   Lloyd.
>
>
>
> On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote:
>> Hi,
>>   I have opened a database, created tables and inserted data to it in a
>> single transaction. Then I have closed the data base. Then I reopened
>> the database and tried to update the table entries. But it throws an
>> exception called Database Locked. Where can be the mistake? I am using
>> wxSqlite3 wrapper class. All these are performed from a single program.
>>
>> Thanks,
>>   Lloyd.
>>
>>
>> __
>> Scanned and protected by Email scanner
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>
>
> __
> Scanned and protected by Email scanner
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Simple Content Management
http://www.ceamus.com


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database Locked

[Lloyd]

I have tracked down the problem and found th following.

I have a select statement which stores the result set in a pointer
variable. If the result set of select statement is empty, the program
executes, otherwise it gives a Database Locked error.   

How can I solve this problem ?

Thanks again,
  Lloyd.



On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote:
> Hi,
>   I have opened a database, created tables and inserted data to it in a
> single transaction. Then I have closed the data base. Then I reopened
> the database and tried to update the table entries. But it throws an
> exception called Database Locked. Where can be the mistake? I am using
> wxSqlite3 wrapper class. All these are performed from a single program. 
> 
> Thanks,
>   Lloyd.
> 
> 
> __
> Scanned and protected by Email scanner
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database locked

[Jay Sprenkle]

On 8/21/06, Laura Longo <[EMAIL PROTECTED]> wrote:

Hi all,
I'm a software developer and I'm using sqlite3 for my application in c++. This 
is the problem I've found:
two processes do queries (about 1 query per second) on one database; the 
'select' queries don't have any problem, while 'update' queries find problems 
after 1 or 2 days that the processes are running. In fact, generally the 
'update' queries begin to fail with the exit code 5 (database locked) with 
sqlite3_get_table(), while the 'select' queries are ok. If I stop and restart 
one of the two processes, this process does for some days all the queries in 
the right way and then begins again to give problems, while the other continues 
to return 'database locked' error.
I don't understand the problem... Am I doing something wrong? Can anyone help 
me?


Are you checking for database locked return codes and retrying the
operation (on updates)?


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database locked. Any idea ?

[Mario . Hebert]

I could not port my code quickly to Cygwin but a quick investigation shows 
me that the lock (wrFlag) is never set back to 1. Which API is supposed to 
do this ?

Right now, I have the following stack trace:

sqlite3_step
sqlite3VbdeExec
Cp_OP_OpenRead
Sqlite3BtreeCursor
wrFlag = 0;

Any idea how my table should be unlocked ? 

Mario Hebert
Legerity



[EMAIL PROTECTED] 
06/20/2006 03:07 PM
Please respond to
sqlite-users@sqlite.org


To
sqlite-users@sqlite.org
cc

Subject
Re: [sqlite] Database locked. Any idea ?






[EMAIL PROTECTED] wrote:
> 
> Anyone has an idea of what may be wrong ? Oh yeah, I am running of my 
own 
> port using uCos and a memory database. 
> 

You say you are using a ":memory:" database and this
is happening?

Can you provide a test program running under Linux?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] Database locked. Any idea ?

[Lodewijk Duymaer van Twist]

Hi,

I don't know if I can help you with your problem, but I'm interested in what
you are trying to do, are u using uCos on a Z-World based system?

Kind regards,

Lodewijk Duymaer van Twist

Re: [sqlite] Database locked. Any idea ?

[Mario . Hebert]

Yes I am using a :memory: database created from 3 flash database that were 
previously attached to it. 

I am not quite sure of the amount of work that would be required for me to 
port it to linux. I will try later today to build it under cygwin and see 
if the same behavior occurs. 

Regards,
Mario Hebert
Legerity


 
> Anyone has an idea of what may be wrong ? Oh yeah, I am running of my 
own 
> port using uCos and a memory database. 
> 

You say you are using a ":memory:" database and this
is happening?

Can you provide a test program running under Linux?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Database locked. Any idea ?

[drh]

[EMAIL PROTECTED] wrote:
> 
> Anyone has an idea of what may be wrong ? Oh yeah, I am running of my own 
> port using uCos and a memory database. 
> 

You say you are using a ":memory:" database and this
is happening?

Can you provide a test program running under Linux?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Database Locked Error

[Ritesh Kapoor]

I've tried to copy/paste and simplify the code.

Please assume that the sql query on line #19 is correct, its actually
longer so i truncated it.  getDB() returns _db.

I checked the value of _db after the sqlite3_open() fn call its a valid
handle.

I'm trying to understand fuser and lsof commands and will get back to
you.

Thanks for you help.  If you have any other suggestions then please do
let me know

Thanks
Ritesh

On Tue, 2006-01-31 at 18:02, Christian Smith wrote:
> On Tue, 31 Jan 2006, Ritesh Kapoor wrote:
> 
> >Regarding the configuration of NFS -
> >I have two machines with NFS on them.
> >- if i run the app on machine 1 it works properly
> >- now when i run the app on machine 2 it works properly
> >
> >But if I login to machine 2 from machine 1 and then run the app I get
> >the 'database is locked' error message.
> 
> 
> I assume this is using an NFS file from machine 2 from machine 1?
> 
> 
> >
> >The sequence of statements are -
> >
> >1  if((fp = fopen(fullFileName.c_str(),"w+")))
> >2  {
> >3 fclose(fp);
> >4  }
> >5  else
> >6  {
> >7 printf("Error creating file");
> >8 return;
> >9  }
> 
> 
> This bit is unnecessary. SQLite will create a file if it does not exist.
> 
> 
> >10 int res = sqlite3_open(fullFileName.c_str(), &_db);
> >11 if( res != SQLITE_OK )
> >12 {
> >13 printf("Error opending DB");
> >14 sqlite3_close(_db);
> 
> 
> _db will not be a valid handle if sqlite3_open failed.
> 
> 
> >15 return;
> >16 }
> >17
> >18 sqlite3_trace(getDB(),logSqlQuery,);
> 
> 
> getDB()? Does this return _db?
> 
> 
> >19 string query = "create table XYZ";
> >20 res =  sqlite3_exec(_db, query.c_str(), NULL , 0, );
> 
> 
> This example won't work as "create table XYZ" isn't a valid complete SQL
> statement.
> 
> 
> >21
> >22 if(res == 5)
> >23 
> >
> >
> >After line #20 the value of 'res' is 5.
> >
> >I've even tried to run the app after removing line #18 which contains
> >the sqlite3_trace() fn call.  But that dosen't seem to be the cause.
> >
> >I've gone through the mail archive of this list and saw that this
> >problem did appear for others as well but there the solution was to
> >check if a previously executing sql statement was still not finished
> >with its job and a another sql statement was being executed.
> >
> >Over here the first sql statement's execution returns this error.
> 
> 
> Could be that the incomplete statement is causing problems.
> 
> 
> >
> >Another assumption that I made was that the host machine locks the file
> >after I open a sqlite connection (line #10).  So I should wait for it to
> >release this lock.  I ran the app using GDB and after line #10 I paused
> >the app for some time hoping that the lock would go away but the problem
> >persists even after pausing for 10, 20 and 30 minutes in seperate runs.
> >
> >
> >If NFS is the problem then what configurations do I need to look at and
> >change to make my app work properly?
> 
> 
> Locking is handled by rpc.lockd(8). It should be started by default.
> 
> 
> >
> >What if the problem is with the host machine locking the file or some
> >other process running on my machine or the host machine which locks the
> >file?  Is this possible?
> 
> 
> Possible. Check your on both machines if the file is in use.
> 
> 
> >
> >If yes then is there a way to find out which process is doing this
> >through the entries in /proc directory?
> >
> 
> 
> If on Linux, try using fuser(1) to see if other processes have the file
> open, and lsof(8) to see if the file has locks associated with it.
> 
> 
> >
> >Thanks,
> >Ritesh
> >
> >
> >
> >On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
> >> Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> >> > Yes.
> >> > My machine has NFS and the machines I log onto also have NFS.  But if
> >> > this is the problem then why dosen't it appear on my machine when I run
> >> > the app.
> >>
> >> Perhaps you are using a local filesystem when you run on
> >> your machine.  Or perhaps NFS is configured properly on
> >> your machine but not on the other machines.
> >>
> >>
> >> > Is there a workaround for this? without having to change the file system
> >> > from NFS.
> >> >
> >>
> >> Yes.  Configure your NFS so that file locking works correctly.
> >>
> >> --
> >> D. Richard Hipp   <[EMAIL PROTECTED]>
> >>
> >
> >

Re: [sqlite] Database Locked Error

[Christian Smith]

On Tue, 31 Jan 2006, Ritesh Kapoor wrote:

>Regarding the configuration of NFS -
>I have two machines with NFS on them.
>- if i run the app on machine 1 it works properly
>- now when i run the app on machine 2 it works properly
>
>But if I login to machine 2 from machine 1 and then run the app I get
>the 'database is locked' error message.


I assume this is using an NFS file from machine 2 from machine 1?


>
>The sequence of statements are -
>
>1  if((fp = fopen(fullFileName.c_str(),"w+")))
>2  {
>3 fclose(fp);
>4  }
>5  else
>6  {
>7 printf("Error creating file");
>8 return;
>9  }


This bit is unnecessary. SQLite will create a file if it does not exist.


>10 int res = sqlite3_open(fullFileName.c_str(), &_db);
>11 if( res != SQLITE_OK )
>12 {
>13 printf("Error opending DB");
>14 sqlite3_close(_db);


_db will not be a valid handle if sqlite3_open failed.


>15 return;
>16 }
>17
>18 sqlite3_trace(getDB(),logSqlQuery,);


getDB()? Does this return _db?


>19 string query = "create table XYZ";
>20 res =  sqlite3_exec(_db, query.c_str(), NULL , 0, );


This example won't work as "create table XYZ" isn't a valid complete SQL
statement.


>21
>22 if(res == 5)
>23 
>
>
>After line #20 the value of 'res' is 5.
>
>I've even tried to run the app after removing line #18 which contains
>the sqlite3_trace() fn call.  But that dosen't seem to be the cause.
>
>I've gone through the mail archive of this list and saw that this
>problem did appear for others as well but there the solution was to
>check if a previously executing sql statement was still not finished
>with its job and a another sql statement was being executed.
>
>Over here the first sql statement's execution returns this error.


Could be that the incomplete statement is causing problems.


>
>Another assumption that I made was that the host machine locks the file
>after I open a sqlite connection (line #10).  So I should wait for it to
>release this lock.  I ran the app using GDB and after line #10 I paused
>the app for some time hoping that the lock would go away but the problem
>persists even after pausing for 10, 20 and 30 minutes in seperate runs.
>
>
>If NFS is the problem then what configurations do I need to look at and
>change to make my app work properly?


Locking is handled by rpc.lockd(8). It should be started by default.


>
>What if the problem is with the host machine locking the file or some
>other process running on my machine or the host machine which locks the
>file?  Is this possible?


Possible. Check your on both machines if the file is in use.


>
>If yes then is there a way to find out which process is doing this
>through the entries in /proc directory?
>


If on Linux, try using fuser(1) to see if other processes have the file
open, and lsof(8) to see if the file has locks associated with it.


>
>Thanks,
>Ritesh
>
>
>
>On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
>> Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
>> > Yes.
>> > My machine has NFS and the machines I log onto also have NFS.  But if
>> > this is the problem then why dosen't it appear on my machine when I run
>> > the app.
>>
>> Perhaps you are using a local filesystem when you run on
>> your machine.  Or perhaps NFS is configured properly on
>> your machine but not on the other machines.
>>
>>
>> > Is there a workaround for this? without having to change the file system
>> > from NFS.
>> >
>>
>> Yes.  Configure your NFS so that file locking works correctly.
>>
>> --
>> D. Richard Hipp   <[EMAIL PROTECTED]>
>>
>
>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] Database Locked Error

[Ritesh Kapoor]

Please ommit line #18 from the sequence of statements
and then reply.

Thanks
Ritesh

On Tue, 2006-01-31 at 16:42, Ritesh Kapoor wrote:
> Regarding the configuration of NFS -
> I have two machines with NFS on them.
> - if i run the app on machine 1 it works properly
> - now when i run the app on machine 2 it works properly
> 
> But if I login to machine 2 from machine 1 and then run the app I get
> the 'database is locked' error message.
> 
> The sequence of statements are -
> 
> 1  if((fp = fopen(fullFileName.c_str(),"w+"))) 
> 2  {
> 3 fclose(fp);
> 4  }
> 5  else
> 6  {
> 7 printf("Error creating file");
> 8 return;
> 9  }
> 10 int res = sqlite3_open(fullFileName.c_str(), &_db);
> 11 if( res != SQLITE_OK )
> 12 {
> 13 printf("Error opending DB");
> 14 sqlite3_close(_db);
> 15 return;
> 16 }
> 17 
> 18 sqlite3_trace(getDB(),logSqlQuery,);
> 19 string query = "create table XYZ";
> 20 res =  sqlite3_exec(_db, query.c_str(), NULL , 0, );
> 21
> 22 if(res == 5)
> 23 
> 
> 
> After line #20 the value of 'res' is 5.  
> 
> I've even tried to run the app after removing line #18 which contains
> the sqlite3_trace() fn call.  But that dosen't seem to be the cause.
> 
> I've gone through the mail archive of this list and saw that this
> problem did appear for others as well but there the solution was to
> check if a previously executing sql statement was still not finished
> with its job and a another sql statement was being executed.
> 
> Over here the first sql statement's execution returns this error.
> 
> Another assumption that I made was that the host machine locks the file
> after I open a sqlite connection (line #10).  So I should wait for it to
> release this lock.  I ran the app using GDB and after line #10 I paused
> the app for some time hoping that the lock would go away but the problem
> persists even after pausing for 10, 20 and 30 minutes in seperate runs.
> 
> 
> If NFS is the problem then what configurations do I need to look at and
> change to make my app work properly?
> 
> What if the problem is with the host machine locking the file or some
> other process running on my machine or the host machine which locks the
> file?  Is this possible?  
> 
> If yes then is there a way to find out which process is doing this
> through the entries in /proc directory?
> 
> 
> Thanks,
> Ritesh
> 
> 
> 
> On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
> > Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> > > Yes.
> > > My machine has NFS and the machines I log onto also have NFS.  But if
> > > this is the problem then why dosen't it appear on my machine when I run
> > > the app.
> > 
> > Perhaps you are using a local filesystem when you run on 
> > your machine.  Or perhaps NFS is configured properly on
> > your machine but not on the other machines.
> > 
> > 
> > > Is there a workaround for this? without having to change the file system
> > > from NFS.
> > > 
> > 
> > Yes.  Configure your NFS so that file locking works correctly.
> > 
> > --
> > D. Richard Hipp   <[EMAIL PROTECTED]>
> > 

Re: [sqlite] Database Locked Error

[Ritesh Kapoor]

Regarding the configuration of NFS -
I have two machines with NFS on them.
- if i run the app on machine 1 it works properly
- now when i run the app on machine 2 it works properly

But if I login to machine 2 from machine 1 and then run the app I get
the 'database is locked' error message.

The sequence of statements are -

1  if((fp = fopen(fullFileName.c_str(),"w+"))) 
2  {
3 fclose(fp);
4  }
5  else
6  {
7 printf("Error creating file");
8 return;
9  }
10 int res = sqlite3_open(fullFileName.c_str(), &_db);
11 if( res != SQLITE_OK )
12 {
13 printf("Error opending DB");
14 sqlite3_close(_db);
15 return;
16 }
17 
18 sqlite3_trace(getDB(),logSqlQuery,);
19 string query = "create table XYZ";
20 res =  sqlite3_exec(_db, query.c_str(), NULL , 0, );
21
22 if(res == 5)
23 


After line #20 the value of 'res' is 5.  

I've even tried to run the app after removing line #18 which contains
the sqlite3_trace() fn call.  But that dosen't seem to be the cause.

I've gone through the mail archive of this list and saw that this
problem did appear for others as well but there the solution was to
check if a previously executing sql statement was still not finished
with its job and a another sql statement was being executed.

Over here the first sql statement's execution returns this error.

Another assumption that I made was that the host machine locks the file
after I open a sqlite connection (line #10).  So I should wait for it to
release this lock.  I ran the app using GDB and after line #10 I paused
the app for some time hoping that the lock would go away but the problem
persists even after pausing for 10, 20 and 30 minutes in seperate runs.


If NFS is the problem then what configurations do I need to look at and
change to make my app work properly?

What if the problem is with the host machine locking the file or some
other process running on my machine or the host machine which locks the
file?  Is this possible?  

If yes then is there a way to find out which process is doing this
through the entries in /proc directory?


Thanks,
Ritesh



On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
> Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> > Yes.
> > My machine has NFS and the machines I log onto also have NFS.  But if
> > this is the problem then why dosen't it appear on my machine when I run
> > the app.
> 
> Perhaps you are using a local filesystem when you run on 
> your machine.  Or perhaps NFS is configured properly on
> your machine but not on the other machines.
> 
> 
> > Is there a workaround for this? without having to change the file system
> > from NFS.
> > 
> 
> Yes.  Configure your NFS so that file locking works correctly.
> 
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
> 

Re: [sqlite] Database Locked Error

[drh]

Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> Yes.
> My machine has NFS and the machines I log onto also have NFS.  But if
> this is the problem then why dosen't it appear on my machine when I run
> the app.

Perhaps you are using a local filesystem when you run on 
your machine.  Or perhaps NFS is configured properly on
your machine but not on the other machines.


> Is there a workaround for this? without having to change the file system
> from NFS.
> 

Yes.  Configure your NFS so that file locking works correctly.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Database Locked Error

[Ritesh Kapoor]

Yes.
My machine has NFS and the machines I log onto also have NFS.  But if
this is the problem then why dosen't it appear on my machine when I run
the app.
Is there a workaround for this? without having to change the file system
from NFS.

Thanks,
Ritesh

On Mon, 2006-01-30 at 18:12, [EMAIL PROTECTED] wrote:
> Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> > 
> > Can anyone suggest what the problem is with sqlite when running apps on
> > different machines. 
> 
> This happens sometimes when NFS us misconfigured so that it
> does not support fcntl() file locks.  The lock requests always
> fail, hence SQLite always returns "database is locked".
> 
> Are you using NFS?
> 
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
> 

Re: [sqlite] Database Locked Error

[drh]

Ritesh Kapoor <[EMAIL PROTECTED]> wrote:
> 
> Can anyone suggest what the problem is with sqlite when running apps on
> different machines. 

This happens sometimes when NFS us misconfigured so that it
does not support fcntl() file locks.  The lock requests always
fail, hence SQLite always returns "database is locked".

Are you using NFS?

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Database locked after crash

[Ara.T.Howard]

On Tue, 17 May 2005, Jaap Krabbendam wrote:
I have been simulating a crash during a transaction. After BEGIN, at some
point I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a
crash.
After that, I can see that a -journal file is present. If I restart my
executable, it seems that the changes of the transaction are made undone
(which is as expected). The journal file however is not removed.
Furthermore, if I try to do the same operation again (BEGIN + some changes),
I get an SQL_BUSY error code on the first record change (UPDATE/SET).

run fuser on the db and see who has it open.  this cannot happen unless:
  - some other process holds the lock (eg. you are using fastcgi or mod_ruby
and some other process is locking the db)
  - your db in on nfs and you setup is fubar.  btw. i've never seen a
__correct__ nfs setup.  when incorrect locks can get hung on the server
side.
  - there is a kernel bug.
I have the feeling that the OS still has a lock on the database. Any ideas on
how to prevent this or on how to recover from this situation?
again - unless there is a kernel bug (which i doubt as we are using sqlite 
on
many of our linux systems running 100,000's of transactions, even on nfs, with
zero issues in 3 years) the most likely explaination is that another process
does, in fact, hold the lock.
I am using the following setup:
-sqlite-3.2.1
-linux/i686/2.6.9-1.667smp
-application using posix threads. Only one thread is accessing the database.
threads and fcntl based locks do not work as you might expect.  your process
will go into uninterruptable sleep on the call to fcntl if it blocks and this
stops all threads.   what do you mean 'only one posix thread' since ruby
threads are green and not posix??  you mean you have another application using
posixthreads in addition to your rails app?  if so that's certainly the
process holding the lock.
if your db is not on nfs this code will show you how to tell which process
holds the lock:
  jib:~ > cat a.rb
  #
  # http://raa.ruby-lang.org/project/posixlock/
  # http://www.codeforpeople.com/lib/ruby/posixlock/
  #
  require 'posixlock'
  path = ARGV.shift || __FILE__
  File::chmod 0700, path
  f = open path, 'r+'
  if fork
ret = f.lockf File::F_LOCK, 0
pid = Process::pid
puts "parent <#{ pid }> holds lock on <#{ f.path }>"
sleep 2
  else
sleep 1
ret = f.lockf File::F_TEST, 0
ppid = ret
pid = Process::pid
puts "child <#{ pid }> cannot lock <#{ f.path }> because pid <#{ ppid }> holds 
lock"
exit
  end
  jib:~ > ruby a.rb
  parent <23833> holds lock on 
  child <23834> cannot lock  because pid <23833> holds lock
so a simple script like
  require 'posixlock'
  path = open ARGV.shift 'r+'
  ret = f.lockf File::F_TEST, 0
  unless ret.zero?
puts "process <#{ ret }> holds lock on <#{ path }>"
  else
puts "lock on <#{ path }> available "
  end
man fcntl will explain all this further.
kind regards.
-a
--
===
| email :: ara [dot] t [dot] howard [at] noaa [dot] gov
| phone :: 303.497.6469
| renunciation is not getting rid of the things of this world, but accepting
| that they pass away. --aitken roshi
===

Re: [sqlite] Database locked after crash

[Dan Kennedy]

Can you post code?

--- Jaap Krabbendam <[EMAIL PROTECTED]> wrote:

> 
> Hi,
> 
> I have been simulating a crash during a transaction. After BEGIN, at some 
> point
> I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a crash.
> 
> After that, I can see that a -journal file is present. If I restart my 
> executable, it seems that the changes of the transaction are made undone 
> (which is as expected). The journal file however is not removed. 
> Furthermore, if I try to do the same operation again (BEGIN + some changes),
> I get an SQL_BUSY error code on the first record change (UPDATE/SET). 
> 
> I have the feeling that the OS still has a lock on the database. Any ideas on 
> how to prevent this or on how to recover from this situation?
> 
> I am using the following setup:
> -sqlite-3.2.1
> -linux/i686/2.6.9-1.667smp
> -application using posix threads. Only one thread is accessing the database.
> 
> Thanks,
> J.J. Krabbendam
> 
> 




__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

Re: [sqlite] Database locked

[Felipe Lopes]

Thank you!! 

Was just wondering, does it escape the double quotes around the data?? 

Thanx again 

Felipe Lopes 

Em 11 Aug 2004, [EMAIL PROTECTED] escreveu: 

>Felipe Lopes wrote: 
>> 
>> I am trying to import a csv file (ip-to-country to be especific) to a 
>table 
>> on sqlite db. 
>> 
> 
>Version 2.8: 
> 
> COPY FROM ' ' USING DELIMITERS ','; 
> 
>Version 3.0: 
> 
> .mode csv 
> .import 
> 
>-- 
>D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 
> 
>-- 

_
Voce quer um iGMail protegido contra vírus e spams? 
Clique aqui: http://www.igmailseguro.ig.com.br
Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/

Re: [sqlite] Database locked

[D. Richard Hipp]

Brass Tilde wrote:
Version 2.8:
   COPY  FROM '' USING DELIMITERS ',';

You weren't a Clipper/XBase programmer at one time, were you?  :->

Never.  The COPY command in version 2.8 was taken from PostgreSQL.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Database locked

[Brass Tilde]

> > I am trying to import a csv file (ip-to-country to be especific) to a
table
> > on sqlite db.
> >
>
> Version 2.8:
>
> COPY  FROM '' USING DELIMITERS ',';

You weren't a Clipper/XBase programmer at one time, were you?  :->

Re: [sqlite] Database locked

[Mauricio Piacentini]

> I am trying to import a csv file (ip-to-country to be especific) to a
> table
> on sqlite db...It has 5+ lines. Tried to use php and it takes like

If you are using SQLite 2.8.x you can try importing the file with SQLite
Database Browser (sqlitebrowser.sourceforge.net). Use the
FILE->IMPORT->CVS TO TABLE menu item.  Give it a few minutes to insert all
data, it should work.

Regards,
Mauricio
Tabuleiro