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 <loren.kea...@braemarllc.com> escreveu: >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users