>Date: Wed, 17 Jul 2013 17:21:15 +0100
>From: Simon Slavin <slav...@bigfraud.org>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>Subject: Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
>Message-ID: <f0ad438b-1165-419d-99bf-57faf9de5...@bigfraud.org>
>Content-Type: text/plain; charset=us-ascii
>On 16 Jul 2013, at 11:24pm, Loren Keagle <loren.kea...@braemarllc.com> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
