Does SQLite support transactions across multiple databases?

Question

I've done some searching and also read the FAQ on the SQLite site, no luck finding an answer to my question.

It could very well be that my database approach is flawed, but at the moment, I would like to store my data in multiple SQLite3 databases, so that means separate files. I am very worried about data corruption due to my application possibly crashing, or a power outage in the middle of changing data in my tables.

In order to ensure data integrity, I basically need to do this:

begin transaction
modify table(s) in database #1
modify table(s) in database #2
commit, or rollback if error

Is this supported by SQLite? Also, I am using sqlite.net, specifically the latest which is based on SQLite 3.6.23.1.

UPDATE

One more question -- is this something people would usually add to their unit tests? I always unit test databases, but have never had a case like this. And if so, how would you do it? It's almost like you have to pass another parameter to the method like bool test_transaction, and if it's true, throw an exception between database accesses. Then test after the call to make sure the first set of data didn't make it into the other database. But maybe this is something that's covered by the SQLite tests, and should not appear in my test cases.

Answer 1

Yes, SQLite explicitly supports multi-database transactions (see https://www.sqlite.org/atomiccommit.html#_multi_file_commit for technical details). However, there is a fairly large caveat. If the database file is in WAL mode, then:

Transactions that involve changes against multiple ATTACHed databases are atomic for each individual database, but are not atomic across all databases as a set.

Answer 2

Yes transactions works with different sqlite database and even between sqlite and sqlserver. I have tried it couple of times.

Some links and info

From here - Transaction between different data sources.

Since SQLite ADO.NET 2.0 Provider supports transaction enlistement, not only it is possible to perform a transaction spanning several SQLite datasources, but also spanning other database engines such as SQL Server.

Example:

using (DbConnection cn1 = new SQLiteConnection(" ... ") )
using (DbConnection cn2 = new SQLiteConnection(" ... "))
using (DbConnection cn3 = new System.Data.SqlClient.SqlConnection( " ... ") )
using (TransactionScope ts = new TransactionScope() )
{
    cn1.Open(); cn2.Open(); cn3.Open();
    DoWork1( cn1 );
    DoWork2( cn2 );
    DoWork3( cn3 );
    ts.Complete();
}

How to attach a new database:

SQLiteConnection cnn = new SQLiteConnection("Data Source=C:\\myfirstdatabase.db");
cnn.Open();

using (DbCommand cmd = cnn.CreateCommand())
{
  cmd.CommandText = "ATTACH DATABASE 'c:\\myseconddatabase.db' AS [second]";
  cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT COUNT(*) FROM main.myfirsttable INNER JOIN second.mysecondtable ON main.myfirsttable.id = second.mysecondtable.myfirstid";


  object o = cmd.ExecuteScalar();

}