SQLite Forum

Attaching to DB *AFTER* Transaction started

Attaching to DB *AFTER* Transaction started

(1) By anonymous on 2020-09-07 11:14:54 [link] [source]

What is supposed to happen in that case?

Can the attach fail, if the attached-to DB is already locked by another TX?

I have an SQLite-backed server, which shards data (by-project) across DBs,
and when I start a transaction, I cannot know in advance all DBs involved in
the transaction (start-tx and writes are in separate client messages), and I
don't recall reading anything in the doc about that particular corner-case,
which happens to be pretty important to me, I now realize. I could well have
missed it in the doc though... specific doc pointers welcomed.

I could test it I guess, but current-behavior is not the same as documented
and supported behavior, thus this question (to Richard and Dan mostly).

I'm still in JOURNAL mode, but plan to switch to WAL soon, in case it matters.

(2) By Richard Hipp (drh) on 2020-09-07 19:44:03 in reply to 1 [link] [source]

This is a complicated question in that the answer depends on many factors. And because it is such a complex question, I think the following request is reasonable: Please create a login on this forum, using some approximation of your real name, and let's continue the conversation that way.
"Anonymous" is fine for drive-by questions and that is why anonymous posting is enabled. But you are not asking a drive-by question here.

(3) By ddevienne on 2020-09-07 20:45:48 in reply to 2 [link] [source]

Hi Richard. I'm the OP. I'm happy to answer follow-up questions.

(4) By ddevienne on 2020-10-09 07:30:35 in reply to 2 [link] [source]

Hi. I was hoping for some insights on this. Is this not a legitimate question?

This is not a hypothetical question. I really have a server that would
naturally want to do this, and would need to resort to some kind of kludgy
separate transactions on separate connections for what should be a single
logical transaction, making data visible to other sessions too soon.

And is general, such clarifications would be useful to the entire SQLite
community as well. Thanks, --DD

(5) By Richard Hipp (drh) on 2020-10-09 08:22:16 in reply to 4 [source]

Can you give more detail on how current behavior differs from documented and supported behavior? Those should be the same, and are as far as we know. Is there a bug in the documentation (or the code, or both)?

(6) By ddevienne on 2020-10-09 08:48:42 in reply to 5 [link] [source]

The reason for my original email (a month ago) is that I couldn't find any information
about that in the documentation. Have I missed it? Has the documentation changed since?
Given the lack of response in this thread, I assumed not.

My question was what should I expect, not that there was a bug in either
doc or code. (unless one considers lack of information in the doc a bug.)

And to complicate matters, the transaction could mix DBs in Journal and WAL mode too.
I also do not recall reading anything about that case either, although again I could
well have missed it. And why I'm asking these questions here.

SQLite's documentation tends to be very exhaustive about when something is
undefined behavior or not; and since it does not say anything (that I know of)
in these particular cases (begin+attach, or attach-Journal + attach+WAL), I guess
one could assume both are supported, but given these are corner cases I never
thought about before, nor remember reading anything about in the doc or the ML/forum
I prefer to ask the experts. Unlike me, you know whether there are unit tests for
these use-cases, for example.

(7) By Gunter Hick (gunter_hick) on 2020-10-09 09:02:55 in reply to 1 [link] [source]

I have found that the attach will succeed, but the transaction may fail.

Consider process A connected to a.db and process B connected to b.db. Both may issue begin immediate sucessfully. Both may attach to the other process's file and read from it. But both will be locked out of writing, until one issues rollback, at which point it will stall in the subsequent begin immediate until the other process ends it's own transaction.

If both of the processes are already connected to a common file c.db, the second begin immediate will stall (waiting for the lock on c.db).

My guess is that attaching a database does not itself acquire a lock, even if a previous begin immediate acquired locks on all the previously attached databases. Instead, normal locking is performed as required by the statements issued in the transaction.