Re: [sqlite] Concurrent access to database

[Marcello Botrugno]

Il 23/01/2012 16:27, Simon Slavin ha scritto:

On 23 Jan 2012, at 1:56pm, Marcello Botrugno wrote:


When I have four or more threads accessing to the database   I see the error message: 
"database table is locked".
This happens  when a thread begins a new Transaction, probably because another 
thread already has an excluse access to the database.

I supposed that, setting the "busy timeout" to an high value (30 secs in my 
case) should manage this kind of problems, but probably I am wrong or I am not using 
sqlite functions in the right way.

With the 30 second timeone (which, as far as I can tell, you're doing 
correctly) there's no reason for you to see that.

I assume that there are no pauses between your BEGIN and COMMIT statements.  In 
other words, the app doesn't pause for user-input or to do lots of calculations 
while you have the EXCLUSIVE lock.  I also assume you are checking the result 
value from all SQLite calls just in case, one of them unexpectedly returns an 
error.

If possible, you might remove the word 'EXCLUSIVE'.  Just to see whether the 
behaviour changes in any way.  I would also play with the options on the 
_open_v2() call if possible, perhaps just setting SQLITE_OPEN_READWRITE, again, 
just to see if anything changed.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


The problem was in the option  SQLITE_OPEN_SHAREDCACHE, infact removing 
that option the application works fine.
It works fine also using SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_READWRITE or 
SQLITE_OPEN_READWRITE only .


Thanks again for your help.

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

[sqlite] Concurrent access to database

[Marcello Botrugno]

Hi All,
I have an application which starts two or more threads.
Each thread open a separate connection to the database and in each 
transaction, modifies various tables of it.
I use third-party library  to access the SQLite database which make it 
possible to have an uniform access to different DBMS. I do not control 
directly the calls  to sqlite3 funcution, but I have the source code of 
the library so I can see the sequence of calls.


The sqlite3 version I am using is 3.7.9 .

The sequence of actions of a thread is the following:

//--- Start Thread ---


// Open connection to DB
sqlite3 *handle(NULL);
sqlite3_open_v2("C:...\dbase", ,
SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_READWRITE );

sqlite3_busy_timeout(handle, 3);

sqlite3_step("PRAGMA foregn_keys = ON");
sqlite3_reset(..)
sqlite3_clear_bindings(..);

// Begin a new transaction
BEGIN TRANSACTION EXCLUSIVE
...
   SELECT 
INSERT ...
UPDATE ...

COMMIT
...
// Begin a new transaction
BEGIN TRANSACTION EXCLUSIVE
...
   SELECT 
INSERT ...
UPDATE ...
...
COMMIT

sqlite3_close();

//--- END Thread ---


When I have four or more threads accessing to the database   I see the 
error message: "database table is locked".
This happens  when a thread begins a new Transaction, probably because 
another thread already has an excluse access to the database.


I supposed that, setting the "busy timeout" to an high value (30 secs in 
my case) should manage this kind of problems, but probably I am wrong or 
I am not using sqlite functions in the right way.


 Can any one suggest me any strategies for overcome this problem ?

Tank you for your help.

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

[sqlite] Windows Thread-safe

[Marcello Botrugno]

Hi all,
I am new of SQLite and I am developing a multithread application using 
SQLite 3.7.9 on MS Windows.

I am using the precompiled version of SQLite downloaded from www.sqlite.org.

Each thread of the application, opens a private connection to the database.

Im observing that, if for example an SQL command in a transaction is 
uncorrect, the rollback does not work: the previously inserted records 
are not removed.


Executing from sqlite3.exe the command:
 PRAGMA  compile_options;

I get the following data:
ENABLE_FTS3
ENABLE_RTREE
TEMP_STORE=1
THREADSAFE=0

I deduce that the library I am using is not thread safe.
I don't know if the behavior of rollback depends on this but I should 
like to use a version of SQLite copiled with THREADSAFE=1.
Do know if  I need to compile the library by myself or is it possible to 
get a precompiled version of SQLite (threadsafe) somewhere ?


Thanks for any help.


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