SQLite3: Database is sometimes locked when a reader is attached with open_v2

[Tino Lange]

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

[Marcus Grimm]

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

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

[Tino Lange]

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

------------------

Post by Marcus Grimm
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.
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

[Marcus Grimm]

Post by Tino Lange
Hi Marcus,
I have no problem when the reading application gets a lock error because the
writing application has a lock.
-> 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.

Post by Tino Lange
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

Post by Tino Lange
Thanks
Tino
------------------

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

--
Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
Tel: [phone]
Fax: [phone]
--------------------------------------------------
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!"

[Pavel Ivanov]

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

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

[Ken]

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.

Subject: Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2
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.
-> 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
------------------

Post by Marcus Grimm
I'm afraid this is by design of sqlite: Sqlite will

lock

Post by Marcus Grimm
the database during a writing transaction, I think no

matter

Post by Marcus Grimm
if you open a 2nd connection using the readonly flag.
a) retry your read attempt after you receive a busy

error code until

Post by Marcus Grimm
     it proceeds.
b) consider shared cache mode and pragma

read_uncommitted = True;

Post by Marcus Grimm
hope this helps
Marcus

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