[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Rowan Worth]

On 11 August 2015 at 06:22, Simon Slavin  wrote:

> If you did set a timeout then SQLite wais a very short time (a millisecond
> ?) before it tries again, then it waits a little longer, then a little
> longer still, etc..  It gradually increases the time until the total time
> reaches the time you set.  It does not use the number you used as a guide
> for the short times is waits.
>

If you're building sqlite yourself, note that this backoff behaviour is
dependent on a specific preprocessor macro, HAVE_USLEEP. If not set,
sqlite's busy handler sleeps in _one second_ intervals, which is not ideal
for concurrent usage scenarios.

(The above only applies to non-windows platforms. On windows the
incremental backoff is always used.)

-Rowan

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Дмитрий Чепуровский]

Thank you for answers, but I have already solved this problem. I set busy
timeout for both connections.

???, 11 ??? 2015 ?.  Simon Slavin ???:

>
> On 11 Aug 2015, at 2:28am, ch >
> wrote:
>
> > Is this because we don't create and handle savepoints correct?
>
> Have you set a timeout value using the routine I pointed to earlier ?  If
> not, do so.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org 
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Дмитрий Чепуровский]

Thank you for help and explain WAL mode.

2015-08-11 1:22 GMT+03:00 Simon Slavin :

>
> On 10 Aug 2015, at 11:12pm, ??? ???  wrote:
>
> > But what is minimum period?
>
> The minimum period doesn't matter.
>
> The number you set the timeout to is the /maximum/ period.  SQLite will
> continue to try to access the file until that amount of time has passed.
> Then it will give up, returning an error.
>
> But SQLite does not start by waiting that amount of time.  SQLite starts
> off by trying to access the file immediately.  If that fails and you
> haven't set a timeout, then it returns an error.
>
> If you did set a timeout then SQLite wais a very short time (a millisecond
> ?) before it tries again, then it waits a little longer, then a little
> longer still, etc..  It gradually increases the time until the total time
> reaches the time you set.  It does not use the number you used as a guide
> for the short times is waits.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Дмитрий Чепуровский]

But what is minimum period?

2015-08-11 0:56 GMT+03:00 Simon Slavin :

>
> On 10 Aug 2015, at 10:49pm, ??? ???  wrote:
>
> > When I set timeout to 12 ms (2 minutes). It starts work. But as I
> > understand from reading SQLite C interface, when DB in WAL journal mode
> it
> > should work without busytimeout.
> >
> > Can it work without busy timeout?
>
> It is correct that you should need to set a timeout.  There is always a
> period of negotiation about file access, and there is always a chance that
> two connections will try to do this at once.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Дмитрий Чепуровский]

When I set timeout to 12 ms (2 minutes). It starts work. But as I
understand from reading SQLite C interface, when DB in WAL journal mode it
should work without busytimeout.

Can it work without busy timeout?

2015-08-10 17:11 GMT+03:00 Simon Slavin :

>
> On 9 Aug 2015, at 10:37pm, ??? ???  wrote:
>
> > I've got a problem. I'm using sqlite3 in my C++ project. In the log I've
> > got error's *DB is locked error code 5*. As I know error code 5 means,
> that
> > DB is busy.
>
> For testings, please use
>
> 
>
> int sqlite3_busy_timeout(sqlite3*, int ms);
>
> with a timeout of a couple of minutes. (Really.  Minutes.)  You will need
> to set it on all connections, not just the one which is currently reporting
> errors.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[R.Smith]

On 2015-08-10 11:49 PM, ??? ??? wrote:
> When I set timeout to 12 ms (2 minutes). It starts work. But as I
> understand from reading SQLite C interface, when DB in WAL journal mode it
> should work without busytimeout.
>
> Can it work without busy timeout?

It can work without Timeouts - the timeout setting is only to make 
SQLite wait a bit for the DB to become available if it is busy. If you 
want to work without timeouts, you have to check for "SQLITE_BUSY" 
messages and make your program wait and retry the operation. By setting 
a timeout, SQLite simply does this for you.

Also, your assumption about WAL mode is wrong: It allows reading from a 
second connection while writing on one connection, but not writing from 
a second connection. This includes starting immediate transactions 
(requiring a write-lock) from a second connection and such operations as 
DELETE and UPDATE which you say you do use.

There is no way you can do writing from 2 connections without checking 
for SQLITE_BUSY and waiting when it is busy.

Starting these write-operations from the second connection needs to wait 
until the DB is accessible, and once it started writing, then the first 
connection has to wait until the DB is accessible before it can write 
again - so you have to wait for the DB to be accessible. Setting a 
Timeout does this automatically for you.

More info here:
https://www.sqlite.org/wal.html#concurrency

As to the minimum period: There is no minimum period, you have to 
understand what your database will do and how long it will write, how 
slow is your disk/media and make a timeout that is suitable. You can 
make the timeout very large, but if something goes wrong, your program 
will "hang" until the time runs out before it gives an error - so don't 
make it too large - and - test it really well.


>
> 2015-08-10 17:11 GMT+03:00 Simon Slavin :
>
>> On 9 Aug 2015, at 10:37pm, ??? ???  wrote:
>>
>>> I've got a problem. I'm using sqlite3 in my C++ project. In the log I've
>>> got error's *DB is locked error code 5*. As I know error code 5 means,
>> that
>>> DB is busy.
>> For testings, please use
>>
>> 
>>
>> int sqlite3_busy_timeout(sqlite3*, int ms);
>>
>> with a timeout of a couple of minutes. (Really.  Minutes.)  You will need
>> to set it on all connections, not just the one which is currently reporting
>> errors.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Simon Slavin]

On 10 Aug 2015, at 11:12pm, ??? ???  wrote:

> But what is minimum period?

The minimum period doesn't matter.

The number you set the timeout to is the /maximum/ period.  SQLite will 
continue to try to access the file until that amount of time has passed.  Then 
it will give up, returning an error.

But SQLite does not start by waiting that amount of time.  SQLite starts off by 
trying to access the file immediately.  If that fails and you haven't set a 
timeout, then it returns an error.

If you did set a timeout then SQLite wais a very short time (a millisecond ?) 
before it tries again, then it waits a little longer, then a little longer 
still, etc..  It gradually increases the time until the total time reaches the 
time you set.  It does not use the number you used as a guide for the short 
times is waits.

Simon.

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Simon Slavin]

On 10 Aug 2015, at 10:49pm, ??? ???  wrote:

> When I set timeout to 12 ms (2 minutes). It starts work. But as I
> understand from reading SQLite C interface, when DB in WAL journal mode it
> should work without busytimeout.
> 
> Can it work without busy timeout?

It is correct that you should need to set a timeout.  There is always a period 
of negotiation about file access, and there is always a chance that two 
connections will try to do this at once.

Simon.

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Simon Slavin]

On 9 Aug 2015, at 10:37pm, ??? ???  wrote:

> I've got a problem. I'm using sqlite3 in my C++ project. In the log I've
> got error's *DB is locked error code 5*. As I know error code 5 means, that
> DB is busy.

For testings, please use



int sqlite3_busy_timeout(sqlite3*, int ms);

with a timeout of a couple of minutes. (Really.  Minutes.)  You will need to 
set it on all connections, not just the one which is currently reporting errors.

Simon.

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

[Дмитрий Чепуровский]

Hello!
I've got a problem. I'm using sqlite3 in my C++ project. In the log I've
got error's *DB is locked error code 5*. As I know error code 5 means, that
DB is busy. To solve this I started to use WAL journal mode. But it doesn't
help.

In my program I've got 2 connections to the same db. I use mutexes for both
DB connections.
I'm opening connections with this code:








*if (sqlite3_open_v2(db_path.c_str(), >db, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, 0) ) {
LOG4CPLUS_FATAL(this->logger, "Can not open/create DB " <<
sqlite3_errmsg(db));sqlite3_close(this->db);}if (sqlite3_exec(this->db,
"PRAGMA journal_mode = WAL;", 0, 0, )) {
LOG4CPLUS_ERROR(this->logger, "SQL det journal mode error: " << err);
sqlite3_free(err);}*

First connection is used for inserting data to the DB. It happens with 4
time every second.
Second connection is used for starting transaction, selecting, updating,
deleting data and committing. It happens every 5 seconds.

I'm getting errors from the first connection.

Please help me to solve this problem.

P.S. Sorry for my bad English