Bad practice to open AND attach the same database?
(1.1) Originally by anonymous with edits by Richard Hipp (drh) on 2021-01-25 11:53:42 from 1.0 [link] [source]
I imagine this has some nasty consequences:
sqlite> .open ./db/chinook.db sqlite> attach database 'd:\sqlite32\db\chinook.db' as chin; sqlite> .databases main: D:\SQLite32\db\chinook.db r/w chin: d:\sqlite32\db\chinook.db r/w
Clearly, it is possible to do this.
What are the likely consequences?
I can't dream up any "nasty consequences". You have simply attached the same database file to a connection twice. I do not see any purpose nor any direct harm from doing so.
You might run into some two-phase commit problems if you attempt to update both attached copies of the same database from the same connection or with wal mode, but I would presume that SQLite3 would simply raise some sort of error condition if it had a problem and prevent corruption of the database file.
In other words, it would be my expectation that if you tried to do something that was problematic it would be detected and you would be told that you cannot do that, the data would be safe, and you would be the unhappy holder of an error return as a consequence of your actions. In other words, that the world would unfold as it should.
Anything which causes the data to be corrupted would be a bug in SQLite3 and should be fixed so that condition is prevented from causing corruption.
I can tell you that there is a widely used program which does this. Not in the shell tool but in C code. The program opens a database, then uses the C API to
ATTACH a few databases including the original one. It works fine. Interlaced use of access to both 'names' of the database work as they should do.
If you update the database using one name and select from it using the other name in the same transaction, you'll get a SQLITE_LOCKED error no later than when you try to commit.
If you update the database using both names in the same transaction, you'll get a SQLITE_LOCKED error on the relevant statement.
If you do BEGIN IMMEDIATE TRANSACTION, you'll get a SQLITE_LOCKED error immediately.
Summary: don't. If you use an in-memory main database, you can safely attach whatever set of on-disk databases you need (each one at most once).