Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Deepak Hegde
Hi All,


There is a problem we are facing with the multiple DB process write operation.

Following the procedure:


1) Open the Database in the two process as:

sqlite3_open_v2 (pcDbName, &psRaceSqlite->sSqliteInfo.pSqlHandle,          (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX),  NULL);

2) Both the link are added the busy handler and busy handler function is retries for 10000 times with 10ms second of delay.
sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle,           dbm_sqlite_busy_handler,psRaceSqlite);


3) In one thread there is a continuous record insertion with begin and commit operation. This begin and commit will be done with for every 200 record insertion. time for a single begin to the commit is about 1 second.

4) If we try to write INSERT or UPDATE in the other thread using sqlite3_exec () then it is giving the error.

Following is the failure cases:
1) Busy handler is invoked, but it will not get success for 10000 retry and then give error.
2) Some time direct error is given without invoking the the busy handler.

Please can anyone help us to understand the use of the multi-thread handling of the sqlite?

Thanks and Regards
Deepak

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Igor Tandetnik-2
On 10/4/2017 10:17 PM, Hegde, Deepakakumar (D.) wrote:

> There is a problem we are facing with the multiple DB process write operation.
>
> Following the procedure:
>
>
> 1) Open the Database in the two process as:
>
> sqlite3_open_v2 (pcDbName, &psRaceSqlite->sSqliteInfo.pSqlHandle,          (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX),  NULL);
>
> 2) Both the link are added the busy handler and busy handler function is retries for 10000 times with 10ms second of delay.
> sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle,           dbm_sqlite_busy_handler,psRaceSqlite);
>
>
> 3) In one thread there is a continuous record insertion with begin and commit operation. This begin and commit will be done with for every 200 record insertion. time for a single begin to the commit is about 1 second.

Use BEGIN IMMEDIATE or BEGIN EXCLUSIVE here. For rationale, see

http://sqlite.org/c3ref/busy_handler.html

the paragraph that mentions "deadlock". Also http://sqlite.org/lang_transaction.html and http://sqlite.org/lockingv3.html

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Simon Slavin-3
In reply to this post by Deepak Hegde


On 5 Oct 2017, at 3:17am, Hegde, Deepakakumar (D.) <[hidden email]> wrote:

> 2) Both the link are added the busy handler and busy handler function is retries for 10000 times with 10ms second of delay.
> sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle,           dbm_sqlite_busy_handler,psRaceSqlite);

Instead of this, just set an timeout, which in your case would be 1000000ms.  This may look big but is an appropriate value.

<https://sqlite.org/c3ref/busy_timeout.html>

SQLite’s own timeout routine knows more about how SQLite works than yours do.  It implements exponential backoff and other clever things.  Do not try to use both at once: strip out your use of sqlite3_busy_handler() .

Remember that you need to set timeout for every connection separately, in other words not only for the connections doing IHSERT but for the other processes which are reading or doing individual INSERT commands.

> 3) In one thread there is a continuous record insertion with begin and commit operation. This begin and commit will be done with for every 200 record insertion. time for a single begin to the commit is about 1 second.

Use BEGIN EXCLUSIVE.

After each COMMIT, pause for at least 20ms.  If that doesn’t improve things you may need more — anything up to 1000ms could help.  You could set that number for testing then reduce it until it stops working.

Thank you for your detailed description and timings which help us make good suggestions.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Deepak Hegde
Hi,


Thanks a lot for this suggestion.


I have reduced the delay in the busy handler from 10ms to 1ms and added a delay of 2ms after "commit" and before "begin". With this it is working fine now.


After reading you input we got to know the root cause is because of the CPU context switch between the process. whenever busy handle start executing another process would have started the "begin" causing the DB lock.


Thanks again.


Thanks and Regards

Deepak

________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Thursday, October 5, 2017 8:00:31 AM
To: SQLite mailing list
Subject: Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.



On 5 Oct 2017, at 3:17am, Hegde, Deepakakumar (D.) <[hidden email]> wrote:

> 2) Both the link are added the busy handler and busy handler function is retries for 10000 times with 10ms second of delay.
> sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle,           dbm_sqlite_busy_handler,psRaceSqlite);

Instead of this, just set an timeout, which in your case would be 1000000ms.  This may look big but is an appropriate value.

<https://sqlite.org/c3ref/busy_timeout.html>

SQLite’s own timeout routine knows more about how SQLite works than yours do.  It implements exponential backoff and other clever things.  Do not try to use both at once: strip out your use of sqlite3_busy_handler() .

Remember that you need to set timeout for every connection separately, in other words not only for the connections doing IHSERT but for the other processes which are reading or doing individual INSERT commands.

> 3) In one thread there is a continuous record insertion with begin and commit operation. This begin and commit will be done with for every 200 record insertion. time for a single begin to the commit is about 1 second.

Use BEGIN EXCLUSIVE.

After each COMMIT, pause for at least 20ms.  If that doesn’t improve things you may need more — anything up to 1000ms could help.  You could set that number for testing then reduce it until it stops working.

Thank you for your detailed description and timings which help us make good suggestions.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

Richard Hipp-3
In reply to this post by Deepak Hegde
On 10/4/17, Hegde, Deepakakumar (D.) <[hidden email]> wrote:

>
>
> 1) Open the Database in the two process
>
> 2) Both the link are added the busy handler and busy handler function is
> retries for 10000 times with 10ms second of delay.
>
> 3) In one thread there is a continuous record insertion with begin and
> commit operation. This begin and commit will be done with for every 200
> record insertion. time for a single begin to the commit is about 1 second.

Because SQLite does not have a separate server process to coordinate
access, only a single process at a time can be writing to the
database.

It sounds like your "continuous insertion" process is monopolizing
write access.  That process seems to always be in between BEGIN and
COMMIT.  Probably you are running BEGIN almost immediately after each
COMMIT, no?  That means that other processes are unable to write
unless they get looking and try to write in that very brief interval
in between COMMIT and the next BEGIN.

Probably your solution is to accumulate data in memory for about 1
second.  Then, once per second, run a BEGIN, lots of INSERTs, and a
COMMIT.  The idea is for there to be a short amount of time in between
BEGIN and COMMIT (a few milliseconds) and a much larger amount of time
between COMMIT and the next BEGIN (nearly one second).

That way, when the process below tries to write, it is likely to hit
in between the COMMIT and the BEGIN and be successful.  Or, if it is
unlucky and hits in between BEGIN and COMMIT, it will get a busy
callback until the next COMMIT occurs.

I think you will do well to always run "BEGIN IMMEDIATE".  That way,
any busy timeout will occur on your BEGIN statement, and not on the
INSERT or UPDATE or COMMIT that follows, and you never have to worry
about retrying the transaction.

>
> 4) If we try to write INSERT or UPDATE in the other thread using
> sqlite3_exec () then it is giving the error.
>
> Following is the failure cases:
> 1) Busy handler is invoked, but it will not get success for 10000 retry and
> then give error.
> 2) Some time direct error is given without invoking the the busy handler.
>
> Please can anyone help us to understand the use of the multi-thread handling
> of the sqlite?
>
> Thanks and Regards
> Deepak
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users