Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2
Tino Lange wrote: > Hi Marcus, > > I have no problem when the reading application gets a lock error because the > writing application has a lock. > > But the problem is the other way around: > -> The writing application gets a lock error because someone reads! well... since the writer blocks the database for read and write access he has to somehow verify that no read is ongoing before he will be able to lock the database. hence if the write locks the database it will indirectly cause that a read locks the db for writers as well. so, the only thing that does not block are read against reads. as I said the shared cache and the read_uncommitted pragma increases this sort of concurrency and I use it regulary in my server application. > > This is what I can't understand and what I didn't expect. I would expect > that the writing application is undisturbed by any readers that open with > SQLITE_OPEN_READONLY. I'm not familiar with that option but as far as I understand it will only prevent that connection to apply any write operation and as I tried to explain: a simple read may very well cause a timeout on a write attempt. Marcus > > Thanks > > Tino > > -- > > > Marcus Grimm wrote: > >> I'm afraid this is by design of sqlite: Sqlite will lock >> the database during a writing transaction, I think no matter >> if you open a 2nd connection using the readonly flag. >> >> the typical solutions are: >> a) retry your read attempt after you receive a busy error code until >> it proceeds. >> b) consider shared cache mode and pragma read_uncommitted = True; >> >> hope this helps >> >> Marcus > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt Tel: +49(0)6151-95147-10 Fax: +49(0)6151-95147-20 -- MedCom slogans of the month: "Vacation ? -- Every day at MedCom is a paid vacation!" "Friday I have monday in my mind." "MedCom -- Every week a vacation, every day an event, every hour a cliffhanger, every minute a climax." "Damned, it's weekend again!" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2
This is by design. The read only transaction acquires a "Read" Lock on the database file. So if that lock has not been released your writing process will receive the SQLITE_LOCKED error code. a. Use a begin exclusive on your writing process and test for sqlite locked. Using a loop and retry logic. The read uncommitted pragma will only work with multi threaded applications not multi process so I don't think this will work for you. The technical reason behind this is that SQLITE does not implement multi versioning of its internal pages. If it did this would allow readers and writers to co-exist with out locking each other out. --- On Thu, 7/2/09, Tino Lange wrote: > From: Tino Lange > Subject: Re: [sqlite] SQLite3: Database is sometimes locked when a reader is > attached with open_v2 > To: sqlite-users@sqlite.org > Date: Thursday, July 2, 2009, 9:45 AM > Hi Marcus, > > I have no problem when the reading application gets a lock > error because the > writing application has a lock. > > But the problem is the other way around: > -> The writing application gets a lock error because > someone reads! > > This is what I can't understand and what I didn't expect. I > would expect > that the writing application is undisturbed by any readers > that open with > SQLITE_OPEN_READONLY. > > Thanks > > Tino > > -- > > > Marcus Grimm wrote: > > > I'm afraid this is by design of sqlite: Sqlite will > lock > > the database during a writing transaction, I think no > matter > > if you open a 2nd connection using the readonly flag. > > > > the typical solutions are: > > a) retry your read attempt after you receive a busy > error code until > > it proceeds. > > b) consider shared cache mode and pragma > read_uncommitted = True; > > > > hope this helps > > > > Marcus > > > ___ > 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] SQLite3: Database is sometimes locked when a reader is attached with open_v2
Hi Marcus, I have no problem when the reading application gets a lock error because the writing application has a lock. But the problem is the other way around: -> The writing application gets a lock error because someone reads! This is what I can't understand and what I didn't expect. I would expect that the writing application is undisturbed by any readers that open with SQLITE_OPEN_READONLY. Thanks Tino -- Marcus Grimm wrote: > I'm afraid this is by design of sqlite: Sqlite will lock > the database during a writing transaction, I think no matter > if you open a 2nd connection using the readonly flag. > > the typical solutions are: > a) retry your read attempt after you receive a busy error code until > it proceeds. > b) consider shared cache mode and pragma read_uncommitted = True; > > hope this helps > > Marcus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2
>From http://www.sqlite.org/lockingv3.html: -- Eventually, the writing process will want to update the database file, either because its memory cache has filled up or because it is ready to commit its changes. Before this happens, the writer must make sure no other process is reading the database and ... ... Obtain a PENDING lock and then an EXCLUSIVE lock on the database file. If other processes still have SHARED locks, the writer might have to wait until those SHARED locks clear before it is able to obtain an EXCLUSIVE lock. -- I believe this waiting by writer has some timeout and eventually he will get "locked" error if reader doesn't release its SHARED lock for a long time. Pavel On Thu, Jul 2, 2009 at 9:38 AM, Tino Lange wrote: > Hi all, > > I have written a program that opens a SQLIte3 database and writes in it most > of the time via replace/update. > > If I do select like (no writes, really only reads) statements from some > other process that carefully opens the database with "sqlite3_open_v2(..., > SQLITE_OPEN_READONLY, ...)" the main (writing) application might get > errcode=5 ermmsg='database is locked' errors when it tries to write while > the other application (only!) reads. > > How is that possible? How to prevent? > > I would expect that the reading application might (of course) sometimes get > "database is locked" errors, but the writing application should never. > > [ I have also realized that the sqlite3 commandline tool uses sqlite_open() > and always opens in read/write mode. That's why I wrote my own reading > application using sqlite3_open_v2 ] > > Thanks > > Tino > > ___ > 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] SQLite3: Database is sometimes locked when a reader is attached with open_v2
I'm afraid this is by design of sqlite: Sqlite will lock the database during a writing transaction, I think no matter if you open a 2nd connection using the readonly flag. the typical solutions are: a) retry your read attempt after you receive a busy error code until it proceeds. b) consider shared cache mode and pragma read_uncommitted = True; hope this helps Marcus Tino Lange wrote: > Hi all, > > I have written a program that opens a SQLIte3 database and writes in it most > of the time via replace/update. > > If I do select like (no writes, really only reads) statements from some > other process that carefully opens the database with "sqlite3_open_v2(..., > SQLITE_OPEN_READONLY, ...)" the main (writing) application might get > errcode=5 ermmsg='database is locked' errors when it tries to write while > the other application (only!) reads. > > How is that possible? How to prevent? > > I would expect that the reading application might (of course) sometimes get > "database is locked" errors, but the writing application should never. > > [ I have also realized that the sqlite3 commandline tool uses sqlite_open() > and always opens in read/write mode. That's why I wrote my own reading > application using sqlite3_open_v2 ] > > Thanks > > Tino > > ___ > 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] SQLite3: Database is sometimes locked when a reader is attached with open_v2
Hi all, I have written a program that opens a SQLIte3 database and writes in it most of the time via replace/update. If I do select like (no writes, really only reads) statements from some other process that carefully opens the database with "sqlite3_open_v2(..., SQLITE_OPEN_READONLY, ...)" the main (writing) application might get errcode=5 ermmsg='database is locked' errors when it tries to write while the other application (only!) reads. How is that possible? How to prevent? I would expect that the reading application might (of course) sometimes get "database is locked" errors, but the writing application should never. [ I have also realized that the sqlite3 commandline tool uses sqlite_open() and always opens in read/write mode. That's why I wrote my own reading application using sqlite3_open_v2 ] Thanks Tino ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
