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

[Richard Hipp]

On 10/4/17, Hegde, Deepakakumar (D.)  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 1 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 1 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hegde, Deepakakumar (D.)]

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  on behalf of 
Simon Slavin 
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.)  
wrote:

> 2) Both the link are added the busy handler and busy handler function is 
> retries for 1 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 100ms.  
This may look big but is an appropriate value.



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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 5 Oct 2017, at 3:17am, Hegde, Deepakakumar (D.)  
wrote:

> 2) Both the link are added the busy handler and busy handler function is 
> retries for 1 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 100ms.  
This may look big but is an appropriate value.



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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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, >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 1 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hegde, Deepakakumar (D.)]

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, >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 1 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 1 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users