CODEWITHSUNDEEP
databasetransactionssqlitesystem.data.sqlite
Dave
Dave

Reputation: 15016

Does SQLite support transactions across multiple databases?

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.

Upvotes: 10

Views: 8205

Answers (2)

Brian Minton
Brian Minton

Reputation: 3777

Yes, SQLite explicitly supports multi-database transactions (see https://www.sqlite.org/atomiccommit.html#_multi_file_commit for technical details):

When multiple database files are modified within a single transaction, all files are updated atomically. In other words, either all of the database files are updated or else none of them are.

However, this is only true for journal modes involving a rollback journal (like the default journal_mode=DELETE). If the database file is in the more performant WAL mode, then the above guarantee is changed to:

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

Upvotes: 4

A G
A G

Reputation: 22577

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();

}

Upvotes: 14

Related Questions