I have the same issue here!
I have an replication system using sqlite where:
- I have 2 databases: one for output* and other for input*;
- I have 2 process accessing booth:
The first is the replicator:
- Get the data on remote server and write on input database;
- Get the data on output database and write on remote server;
The second process:
- Read the input database;
- Write on output database;
In booth of process the databases are attached,
I've used another empty database just to create a connection.
Then I have seen when I replicator commits and the second process tries to
write the error happens,
but I can not sure about this...
I just saying you are not alone.
--
Israel Lins Albuquerque
Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.
Em 18/07/2013, às 16:26, Loren Keagle <[email protected]> escreveu:
>> Date: Wed, 17 Jul 2013 17:21:15 +0100
>> From: Simon Slavin <[email protected]>
>> To: General Discussion of SQLite Database <[email protected]>
>> Subject: Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
>> Message-ID: <[email protected]>
>> Content-Type: text/plain; charset=us-ascii
>
>
>> On 16 Jul 2013, at 11:24pm, Loren Keagle <[email protected]> wrote:
>
>>> Begin EXCLUSIVE TRANSACTION;
>>> insert several rows of data;
>>> Commit transaction;
>>>
>>> Prepare query statement;
>>> Iterate through one or more rows;
>>> Reset statement;
>>>
>>> Attempt to begin transaction; <--- SQLITE_BUSY!!!!
>>> Would like to write more here, but can't unless I close/open the
>>> connection;
>
>> I assume you're checking the result codes returned by all the API calls
>> before the second BEGIN to see that they all return SQLITE_OK.
>
>> Please add a _finalize() after the _reset() just for testing purposes. I
>> know you may not want it as part of your production code.
>
>> Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN
>> EXCLUSIVE ?
>
>> Simon.
>
>
> Thanks for the reply. I've written wrapper classes in C++ that automatically
> check all return codes for every sqlite API call I make. The only return
> error is the SQLITE_BUSY from the transaction statement (It's EXCLUSIVE, btw,
> but it doesn't seem to matter in this context).
>
> I've tried finalizing all statements. It definitely seems to be related to
> having the same database attached multiple times with different names. I've
> done this because my data is split up amongst multiple sub-databases, and I
> simply have a reader and writer object that can work independently. Of
> course, they can both end up pointing at the same sub-database, but I never
> would have thought this was a problem.
>
> I've written some sample code to illustrate my problem. I've commented out
> the actions that don't seem to make any difference. Simply the fact that
> I've attached the second database causes the failure. As soon as I detach
> it, I can write on the first again:
>
> // Open master database
> sqlite3* db = NULL;
> int ret = sqlite3_open_v2("Test.sqlite", &db, SQLITE_OPEN_FULLMUTEX |
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);
> if(ret != SQLITE_OK)
> {
> exit(1);
> }
> sqlite3_extended_result_codes(db, TRUE);
>
> // Create table on main. This probably serves no purpose.
> ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS MainTable (id
> INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
> if(ret != SQLITE_OK)
> exit(3);
>
> // Attach write database
> ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as write1;",
> NULL, NULL, NULL);
> if(ret != SQLITE_OK)
> exit(2);
>
> // Create table on subdb
> ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS write1.TestTable
> (id INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
> if(ret != SQLITE_OK)
> exit(3);
>
> // Insert some data in write table
> sqlite3_stmt * insert = nullptr;
> const char* tail = nullptr;
> ret = sqlite3_prepare_v2(db, "INSERT INTO write1.TestTable (IntColumn)
> VALUES (?1);", -1, &insert, &tail);
> if (ret != SQLITE_OK)
> exit(4);
>
> for (int i = 0; i < 10; ++i)
> {
> ret = sqlite3_bind_int(insert, 1, i);
> if (ret != SQLITE_OK)
> exit(5);
>
> ret = sqlite3_step(insert);
> if(ret != SQLITE_DONE)
> exit(6);
>
> ret = sqlite3_reset(insert);
> if (ret != SQLITE_OK)
> exit(7);
> }
> ret = sqlite3_reset(insert);
> if (ret != SQLITE_OK)
> exit(8);
>
> // Attach read database
> ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as read1;",
> NULL, NULL, NULL);
> if(ret != SQLITE_OK)
> exit(9);
>
> //sqlite3_stmt * readRow = nullptr;
> //ret = sqlite3_prepare_v2(db, "SELECT * FROM read1.TestTable;", -1,
> &readRow, &tail);
> //if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
> // exit(10);
>
> //// Iterate through the inserted rows
> //do
> //{
> // ret = sqlite3_step(readRow);
> // if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
> // exit(11);
>
> // int id = sqlite3_column_int(readRow, 0);
> // int val = sqlite3_column_int(readRow, 1);
> //} while (ret != SQLITE_DONE);
>
> //ret = sqlite3_reset(readRow);
> //if (ret != SQLITE_OK)
> // exit(12);
>
> //// Finalize open read statement. Has no effect on the transaction,
> but is necessary for detaching?
> //ret = sqlite3_finalize(readRow);
> //if (ret != SQLITE_OK)
> // exit(13);
>
> //// Detach read db. This will allow the transaction to succeed.
> //ret = sqlite3_exec(db, "DETACH DATABASE read1;", NULL, NULL, NULL);
> //if(ret != SQLITE_OK)
> // exit(14);
>
> // Now attempt to create a transaction
> ret = sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION;", NULL, NULL,
> NULL);
> if(ret != SQLITE_OK)
> exit(15); // <-- This fails if the 2nd db is attached
>
> This email, including any attachments and files transmitted with it, are for
> the sole use of the intended recipient(s) to whom this email is addressed,
> and may contain confidential and/or privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not the
> intended recipient, please be advised that you have received this email in
> error, and please contact the sender by reply email and destroy all copies
> (including all electronic and hard copies) of the original message. Thank you.
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
