Multithread access to DB

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Multithread access to DB

Rafal Rusin
Hello,

I'm having problems with famous multithread bug
"Database is locked".
I tried to work around that with sqlite3_busy_timeout
or busy_handler, but with no success. I had deadlocks.
I was using version 3.3.4 on Debian Linux.
Could You give me some links to discussion on that
topic?
Maybe there are some future plans to change
implementation so it won't return SQLITE_BUSY
error, but wait? It's quite annoying and should be replaced
with some kind of quality multithread solution.
Maybe with some semaphores or fifos on linux
and similiar things on windows.

Best Regards,
Rafal Rusin

TouK Company
(www.touk.pl)
Reply | Threaded
Open this post in threaded view
|

Re: Multithread access to DB

Jay Sprenkle
On 3/13/06, Rafal Rusin <[hidden email]> wrote:

> Hello,
>
> I'm having problems with famous multithread bug
> "Database is locked".
> I tried to work around that with sqlite3_busy_timeout
> or busy_handler, but with no success. I had deadlocks.
> I was using version 3.3.4 on Debian Linux.
> Could You give me some links to discussion on that
> topic?
> Maybe there are some future plans to change
> implementation so it won't return SQLITE_BUSY
> error, but wait? It's quite annoying and should be replaced
> with some kind of quality multithread solution.
> Maybe with some semaphores or fifos on linux
> and similiar things on windows.

Maybe you want to call this:

"      int sqlite3_busy_timeout(sqlite3*, int ms);

This routine sets a busy handler that sleeps for a while when a table
is locked. The handler will sleep multiple times until at least "ms"
milliseconds of sleeping have been done. After "ms" milliseconds of
sleeping, the handler returns 0 which causes sqlite3_exec() to return
SQLITE_BUSY.

Calling this routine with an argument less than or equal to zero turns
off all busy handlers. "
Reply | Threaded
Open this post in threaded view
|

Re: Multithread access to DB

Rafal Rusin
> Maybe you want to call this:
>
> "      int sqlite3_busy_timeout(sqlite3*, int ms);
>
> This routine sets a busy handler that sleeps for a while when a table
> is locked. The handler will sleep multiple times until at least "ms"
> milliseconds of sleeping have been done. After "ms" milliseconds of
> sleeping, the handler returns 0 which causes sqlite3_exec() to return
> SQLITE_BUSY.
>
> Calling this routine with an argument less than or equal to zero turns
> off all busy handlers. "
>

Like I said, I tried to use it without success. There was a deadlock
when I tried to use it with python pysqlite2 with 10 threads
simultanously accessing DB.

Best Regards,
Rafal Rusin
TouK Company
(www.touk.pl)
Reply | Threaded
Open this post in threaded view
|

Re: Multithread access to DB

Deepak Kaul
I had major problems with multithreaded access to a sqlite database file
on an NFS mount.  While not exactly your scenerio there might be some
similarities.  I had to come up with a crazy scheme to allow only one
thread to access the sqlite database at a time.  See below.

> I came up with a scheme where only one process would handle updating the
> database directly.  All other processes locally or remotely would update
> the database through a file hand shaking protocol.
>
> Here is an example
> Database Updater Process (Server)
> Database Client Process (Client)
>
> Server defines two directories (queries and responses).
>
> Client wants to insert, update or delete data from a database.
> 1.  client creates a file with the necessary information
> 2.  client moves file into queries directory
> 3.  server sees new file in queries directory
> 4.  server parses file
> 5.  server inserts, updates or deletes data from database.
>
> Client wants to select data from a database.
> 1.  client creates a file with the appropriate sql statement
> 2.  client moves file into queries directory
> 3.  server sees new file in queries directory
> 4.  server parses file
> 5.  server preforms select statement
> 6.  server creates response file
> 7.  server moves response file into response directory
> 8.  client sees new response file in response directory
> 9.  client parses file
> 10.  client obtains data
>
> This scheme is preferred over sockets because if the database updater
> process dies you won't lose information.  All inserts, updates and
> deletes will be sitting in the queries directory waiting for the
> database updater process to start again.
>
> This is just one solution to work around the NFS problem I was having.
> If you find NFS does not work for you I would try either some sort of
> sockets implementation or some sort of file hand shaking protocol.

While I thought this was originally an NFS only problem I'm not 100%
convinced it is.  Anyway just thought I would let you know a work around
scheme that completely avoids locks.


Rafal Rusin wrote:

>> Maybe you want to call this:
>>
>> "      int sqlite3_busy_timeout(sqlite3*, int ms);
>>
>> This routine sets a busy handler that sleeps for a while when a table
>> is locked. The handler will sleep multiple times until at least "ms"
>> milliseconds of sleeping have been done. After "ms" milliseconds of
>> sleeping, the handler returns 0 which causes sqlite3_exec() to return
>> SQLITE_BUSY.
>>
>> Calling this routine with an argument less than or equal to zero turns
>> off all busy handlers. "
>>
>>    
>
> Like I said, I tried to use it without success. There was a deadlock
> when I tried to use it with python pysqlite2 with 10 threads
> simultanously accessing DB.
>
> Best Regards,
> Rafal Rusin
> TouK Company
> (www.touk.pl)
>
>  

--
Software Engineer
[hidden email]
301.286.7951
Reply | Threaded
Open this post in threaded view
|

Re: Multithread access to DB

Jay Sprenkle
In reply to this post by Rafal Rusin
On 3/13/06, Rafal Rusin <[hidden email]> wrote:

> > Maybe you want to call this:
> >
> > "      int sqlite3_busy_timeout(sqlite3*, int ms);
> >
> > This routine sets a busy handler that sleeps for a while when a table
> > is locked. The handler will sleep multiple times until at least "ms"
> > milliseconds of sleeping have been done. After "ms" milliseconds of
> > sleeping, the handler returns 0 which causes sqlite3_exec() to return
> > SQLITE_BUSY.
> >
> > Calling this routine with an argument less than or equal to zero turns
> > off all busy handlers. "
> >
>
> Like I said, I tried to use it without success. There was a deadlock
> when I tried to use it with python pysqlite2 with 10 threads
> simultanously accessing DB.

If your code has a deadlock that waiting won't fix have one of the threads
rollback it's transaction and retry it from the beginning. Alternatively
use "begin immediate" around the code that's deadlocking. That will
serialize access to the resources.
Reply | Threaded
Open this post in threaded view
|

Re: Multithread access to DB

Rafal Rusin
Sqlite has 3 types of locks, which is weird for me.
Could You explain what is a "reserved lock"?
Read and read/write (exclusive) locks are apparent.

http://www.sqlite.org/capi3ref.html#sqlite3_busy_handler

Best Regards,
Rafal Rusin

TouK Company
(www.touk.pl)
Reply | Threaded
Open this post in threaded view
|

RE: Multithread access to DB

Drew, Stephen
In reply to this post by Rafal Rusin
http://www.sqlite.org/lockingv3.html

RESERVED
A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently just
reading from the file. Only a single RESERVED lock may be active at one
time, though multiple SHARED locks can coexist with a single RESERVED
lock. RESERVED differs from PENDING in that new SHARED locks can be
acquired while there is a RESERVED lock.  

-----Original Message-----
From: Rafal Rusin [mailto:[hidden email]]
Sent: 21 March 2006 15:03
To: [hidden email]
Subject: Re: [sqlite] Multithread access to DB

Sqlite has 3 types of locks, which is weird for me.
Could You explain what is a "reserved lock"?
Read and read/write (exclusive) locks are apparent.

http://www.sqlite.org/capi3ref.html#sqlite3_busy_handler

Best Regards,
Rafal Rusin

TouK Company
(www.touk.pl)


Reply | Threaded
Open this post in threaded view
|

Speed, Locks and Stability

Deepak Kaul
Please rank the following scenarios considering speed, locks and
stability.  I'm using sqlite in a C++ environment and running in a
single process and single threaded environment where SQL_BUSY should
occur minimally.

1.  Calling sqlite_exec within my C++ program
2.  Calling sqlite_prepare, sqlite3_step and sqlite3_finalize within my
C++ program
3.  Calling ::system with the following string "/usr/bin/sqlite3
database.db < file.txt"
     Where file.txt contains ".read sqlstatements.txt"
     Where sqlstatements.txt contains sql statements to be executed.

Thanks in advance.

--
Software Engineer
[hidden email]
301.286.7951
Reply | Threaded
Open this post in threaded view
|

Re: Speed, Locks and Stability

Dennis Cote
Deepak Kaul wrote:

> Please rank the following scenarios considering speed, locks and
> stability.  I'm using sqlite in a C++ environment and running in a
> single process and single threaded environment where SQL_BUSY should
> occur minimally.
>
> 1.  Calling sqlite_exec within my C++ program
> 2.  Calling sqlite_prepare, sqlite3_step and sqlite3_finalize within
> my C++ program
> 3.  Calling ::system with the following string "/usr/bin/sqlite3
> database.db < file.txt"
>     Where file.txt contains ".read sqlstatements.txt"
>     Where sqlstatements.txt contains sql statements to be executed.
>
Rough order of speed and approximate (guessed) execution time factors

1.   using sqlite_prepare etc. will be fastest  

2.   using sqlite_exec will be slower by 10% to 100% (factor 1.1 to 2)
due to time spent generating and reparsing SQL
       
3.   uses system will be slowest by a larger factor (25% to 200%) due to
overhead of generating, opening, and reading the SQL from the file.

These factors really depend upon the type of SQL you will be using.

If you are working in C++ you might want to consider using a simple C++
wrapper to handle the prepare, step, finalize sequencing for you. I
would suggest http://www.codeproject.com/database/CppSQLite.asp as a
good place to start.

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: Speed, Locks and Stability

John Stanton-3
In reply to this post by Deepak Kaul
If your application runs the same SQL statements more than once, use
prepare, but this way

         sqlite3_prepare
         ...
         multiple
          sqlite3_step
          sqlite3_reset

         at the end
         sqlite3_finalize

Sqlite3_exec is just a wrapper around prepare, step, reset...  Use it if
you are only executing the SQL once.

The last approach is ugly.

Deepak Kaul wrote:

> Please rank the following scenarios considering speed, locks and
> stability.  I'm using sqlite in a C++ environment and running in a
> single process and single threaded environment where SQL_BUSY should
> occur minimally.
>
> 1.  Calling sqlite_exec within my C++ program
> 2.  Calling sqlite_prepare, sqlite3_step and sqlite3_finalize within my
> C++ program
> 3.  Calling ::system with the following string "/usr/bin/sqlite3
> database.db < file.txt"
>     Where file.txt contains ".read sqlstatements.txt"
>     Where sqlstatements.txt contains sql statements to be executed.
>
> Thanks in advance.
>

Reply | Threaded
Open this post in threaded view
|

Re: Speed, Locks and Stability

Clay Dowling
In reply to this post by Deepak Kaul

Deepak Kaul said:

> 1.  Calling sqlite_exec within my C++ program

Good for one-off statements, like DDL.  If you have to generate the SQL
and deal with parameters though, this option is no good.

> 2.  Calling sqlite_prepare, sqlite3_step and sqlite3_finalize within my
> C++ program

Best for multi-use statements, or when use-supplied data needs to be used.

> 3.  Calling ::system with the following string "/usr/bin/sqlite3
> database.db < file.txt"
>      Where file.txt contains ".read sqlstatements.txt"
>      Where sqlstatements.txt contains sql statements to be executed.

Fire the programmer you catch/caught doing this.  Deny that they ever
worked for your organization.

Clay
--
Simple Content Management
http://www.ceamus.com

Reply | Threaded
Open this post in threaded view
|

sql question

Uma Venkataraman
I want to recycle the table for which I need to be able to delete the first
100 records from a table and add 100 new records.

Thanks

Reply | Threaded
Open this post in threaded view
|

Re: sql question

Dennis Cote
Uma Venkataraman wrote:

> I want to recycle the table for which I need to be able to delete the
> first 100 records from a table and add 100 new records.
>
> Thanks
>
>
Uma,

See my reply to a similar question about FIFO tables in the archives at
http://article.gmane.org/gmane.comp.db.sqlite.general/16175/match=fifo

HTH
Dennis Cote