Re: [sqlite] Database locked error, while deleting
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
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
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
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
"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
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
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
