SQLite Forum

Attaching to DB *AFTER* Transaction started
Login

Attaching to DB *AFTER* Transaction started

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

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]

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

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]

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 [link]

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]

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]

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.