I've narrowed this locking issue down to a very simply test case. It seems as
though having the same file attached multiple times with different names
prevents exclusive or immediate transactions from acquiring a lock. Deferred
transactions still seem to work fine. Try the following code:
#define SQLITE_CHECK(s) do { int localret = (s); if (localret != SQLITE_OK)
exit(__LINE__); } while (false);
int main (void)
{
::DeleteFile(L"Test.sqlite");
::DeleteFile(L"TestSub.sqlite");
// Open master database
sqlite3* db = NULL;
SQLITE_CHECK(sqlite3_open_v2("Test.sqlite", &db, SQLITE_OPEN_FULLMUTEX
| SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr));
// Attach write database
SQLITE_CHECK(sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as
write1;", NULL, NULL, NULL));
SQLITE_CHECK(sqlite3_exec(db, "CREATE TABLE write1.TestTable (id
INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL));
// Attach read database
SQLITE_CHECK(sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as
read1;", NULL, NULL, NULL));
//// Detach read db
//SQLITE_CHECK(sqlite3_exec(db, "DETACH DATABASE read1;", NULL, NULL,
NULL));
// Exclusive transactions fail
SQLITE_CHECK(sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION;", NULL,
NULL, NULL));
// Immediate transactions fail
//SQLITE_CHECK(sqlite3_exec(db, "BEGIN IMMEDIATE TRANSACTION;", NULL,
NULL, NULL));
// Deferred transactions are fine
//SQLITE_CHECK(sqlite3_exec(db, "BEGIN DEFERRED TRANSACTION;", NULL,
NULL, NULL));
// Write to the database via the write1 table
SQLITE_CHECK(sqlite3_exec(db, "INSERT INTO write1.TestTable (IntColumn)
VALUES (1);", NULL, NULL, NULL));
SQLITE_CHECK(sqlite3_exec(db, "COMMIT TRANSACTION;", NULL, NULL, NULL));
return 0;
}
Currently my workaround is to conditionally detach the read database if it's
pointed to the same file index as the write database. I'd like to know whether
this is intended behavior. There doesn't seem to be any limitations in the
documentation about attaching the same file multiple times. It seems like it
should be a bug, because it works for deferred transactions.
Can someone knowledgeable in the ATTACH/DETACH behavior comment on this?
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Loren Keagle
> Sent: Monday, July 22, 2013 2:38 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite-users Digest, Vol 67, Issue 18
>
> > "The sqlite3_reset() function is called to reset a prepared statement
> > object back to its initial state, ready to be re-executed. Any SQL
> > statement variables that had values bound to them using the
> > sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings()
> > to reset the bindings."
> >
> > To close a prepared statement you need to use finalize.
> >
> > "The sqlite3_finalize() function is called to delete a prepared
> > statement. If the most recent evaluation of the statement encountered
> > no errors or if the statement is never been evaluated, then
> > sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of
> > statement S failed, then sqlite3_finalize(S) returns the appropriate
> > error code or extended error code."
> >
> > You have one prepared statement open and then try to start a transaction.
> > This gives you a busy error.
>
> I'm sorry, but this doesn't fit with my experience with sqlite at all. I
> create
> transactions all the time while having unfinalized prepared statements. As
> long as the statements are reset, they should not be active, and a "BEGIN
> EXCLUSIVE" does not return SQLITE_BUSY.
>
> This issue seems to be related to having the same file attached with multiple
> database names. See my earlier response with sample code that
> demonstrates the problem.
>
> One experiment I have not yet tried is to finalize and re-prepare any existing
> statements that were prepared against the first attached file, after attaching
> the same file a second time. If that works, then I would still consider it a
> bug
> in sqlite, as the locking subsystem should still behave consistently with no
> active statements.
>
> ~Loren
>
>
>
> > On 07/17/2013 06:56 PM, Du?an Paulovi? wrote:
> > > If you remove a busy check, does it output any statements?
> > > Do you have any custom functions/operations running so they could
> > > block sqlite in creating new statement?
> > >
> > >
> > > 2013/7/17 Loren Keagle <loren.kea...@braemarllc.com>
> > >
> > >> Hi everyone,
> > >>
> > >> I have an interesting locking problem that I'm wondering if someone
> > >> can help with some insight.
> > >>
> > >> I have a master database with some metadata, and several
> > >> sub-databases to store logging events. I have one reader object and
> > >> one writer object that attach to the sub-databases and encapsulate
> > >> the read/write operations respectively.
> > >>
> > >> I've found a very unexpected locking behavior with the attached
> > >> databases and exclusive transactions. One of my unit tests does the
> > following:
> > >>
> > >> 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 can't seem to figure out any reason why I can't create a new
> > >> exclusive transaction here, and I feel it must have to do with the
> > >> fact that I have attached to sub-databases (possibly the same
> > >> sub-database) with my reader/writer objects. This is single
> > >> threaded and only database connection (with attach/detach logic).
> > >> I have verified that all statements prepared by the connection are
> > >> properly reset - this is handled by my C++ wrappers, and any errors
> > >> will throw an exception. I even iterated through all of the current
> > >> statements with the following code immediately before my
> > >> transaction failure, with no
> > >> results:
> > >>
> > >> sqlite3_stmt *stmt = NULL;
> > >> while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL) {
> > >> if (sqlite3_stmt_busy(stmt))
> > >> {
> > >> const char* sql = sqlite3_sql(stmt);
> > >> std::cout << sql << "\r\n";
> > >> }
> > >> }
> > >>
> > >> Can anyone think of a reason why attached databases would prevent
> > >> entering a second transaction? BTW, it doesn't seem to work with
> > >> immediate transactions either. If I remove the query, everything
> > >> works
> > fine.
> > >>
> > >> Thanks!
> > >>
> > >>
> > >> ________________________________
> > >> 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
> > >
> >
> > --
> > ASCII ribbon campaign ( )
> > against HTML e-mail X
> > www.asciiribbon.org / \
> >
>
>
> 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
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
