Howto perform 2-phase commit (global transaction or savepoint)?

[Shalex]

1) To emulate nested transactions I should use savepoint inside a transaction, right?
I've made some sample code - is it correct now?

I have tried your code with the test project you uploaded previously. It inserts/updates records without any errors. What behaviour did you expect to obtain?

2) Regarding the first approach with one connection and one local transaction ("data source" database + "attached" database):
Since I'm using EF Code-First - I will have one DbContext with all my Entities (from both databases), right?
But I didn't catch how can I make my DbContext know that some of it's DbSet's should be saved in first database and others should be saved in second one. Some special attributes in DbContext initialisation?

This approach can be used for predefined (existing) databases.

UPDATE: the further discussion (message 1 and message 2) proves that EF Migrations can create tables in "attached" databases as well.

[bairog]

What behaviour did you expect to obtain?

The main situation for which I need 2-phase commit is:
1) Committing UserTypes into one database is ok - here I emulate a nested transaction commit via a savepoint commit
2) Committing Users into another database (transactionUsers.Commit()) fails for some reason
3) Both transactions (transactionUsers and transactionUserTypes, which is an upper-level transaction for a savepoint commited above) should perform rollback - so finally no data should be saved to both databases

But to tell you the truth I unfortunatelly failed to reproduce 2) step:
- I've tried to modify data in Users database via external tool before calling "transactionUsers.Commit()" - but database is locked
- I've tried to make NOT NULL restriction for User.Age for already existing database and update age to NULL value - but it fails on contextUsers.SaveChanges(), not on transactionUsers.Commit()

So I have no idea howto reproduce the scenario described above..
BTW I've updated my sample project to recent changes

This approach can be used for predefined (existing) databases

Ok, lets say both databases already exist (in my test project - one database stores UserTypes, another one - Users).
Do you mean my DbContext (which have DbSet<UserTypes> and DbSet<Users>) will work out-of-the-box if I use a connection with "Data Source" database+"Atached" database for it?
DbContext will know by himself in which one database it should save Users and in which one - UserTypes?

[Shalex]

Do you mean my DbContext (which have DbSet<UserTypes> and DbSet<Users>) will work out-of-the-box if I use a connection with "Data Source" database+"Atached" database for it?

Yes, we do.

DbContext will know by himself in which one database it should save Users and in which one - UserTypes?

Yes, DbContext will know this if the corresponding tables already exist in two databases (are predefined).

[bairog]

Regarding approach with savepoints:
Finally I've reproduces step 2) of my main scenario which leads to need for 2-phase commit.
I've simply put Users.db on a removable drive/network share an then made it unavailable just before committing Users (just before line transactionUsers.Commit()).
My code worked correctly (no data was saved to both databases). So we will use that approach in our software.

Regarding approach with "Data Source" database+"Attached" database:
I've found one more limitation - databases should be predefined (existing) and no EF migrations are allowed.
If use that code for my context (which describes entities from both databases)

Code: Select all

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, MyDbContextMigrationConfiguration>());

        internal sealed class MyDbContextMigrationConfiguration : DbMigrationsConfiguration<MyDbContext>
        {
            public MyDbContextMigrationConfiguration()
            {
                AutomaticMigrationsEnabled = true;
                AutomaticMigrationDataLossAllowed = false;

                SetSqlGenerator(SQLiteConnectionInfo.InvariantName, new SQLiteEntityMigrationSqlGenerator());
            }
        }

"Data Source" database anyway migrates adding all tables from context (including tables from "Attached" database). Then context never uses "Attached" database, it writes data to newly created tables in "Data Source" database.
So - are my investigations correct?
Or maybe I can make migrations work properly somehow (I mean if some table schema changes - it migrates to actual version, without creating tables from enother database)

[Shalex]

"Data Source" database anyway migrates adding all tables from context (including tables from "Attached" database). Then context never uses "Attached" database, it writes data to newly created tables in "Data Source" database.

We will investigate the issue and notify you about the result.

[Shalex]

"Data Source" database anyway migrates adding all tables from context (including tables from "Attached" database). Then context never uses "Attached" database, it writes data to newly created tables in "Data Source" database.

We will investigate the issue and notify you about the result.

The modelBuilder.Entity<UserType>().ToTable("UserType", "UserType"); entry within the OnModelCreating method maps the UserType table to the UserType schema (UserType.db). The following EF code works with tables from different database files:

Code: Select all

using Devart.Data.SQLite;
using Devart.Data.SQLite.Entity.Migrations;
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.IO;

namespace ConsoleApplication {
    class Program {
        static void Main(string[] args) {

            var monitor = new SQLiteMonitor() { IsActive = true };

            CreateTestDatabases();

            EFAttachDatabase();
        }

        private static void CreateTestDatabases() {

            if (!File.Exists("User.db")) {
                using (var connection = new SQLiteConnection("DataSource=User.db;FailIfMissing=false;")) {
                    connection.Open();
                }
            }

            if (!File.Exists("UserType.db")) {
                using (var connection = new SQLiteConnection("DataSource=UserType.db;FailIfMissing=false;")) {
                    connection.Open();
                }
            }
        }
        
        private static void EFAttachDatabase() {
            
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, MyDbContextMigrationConfiguration>());
            using (var context = new MyDbContext("DataSource=User.db;Attach=UserType.db;")) {
                
                context.Database.Initialize(true);

            }
        }
    }


    internal sealed class MyDbContextMigrationConfiguration : DbMigrationsConfiguration<MyDbContext> {
        public MyDbContextMigrationConfiguration() {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = false;
            SetSqlGenerator(SQLiteConnectionInfo.InvariantName, new SQLiteEntityMigrationSqlGenerator());
        }
    }


    public class User {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public int Age { get; set; }
        public Guid? UserTypeId { get; set; }
    }


    public class UserType {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public int Privilege { get; set; }
    }

    
    [DbConfigurationType(typeof(Devart.Data.SQLite.Entity.SQLiteEntityProviderServicesConfiguration))]
    class MyDbContext : DbContext {

        public MyDbContext() : base(new SQLiteConnection("DataSource=User.db;Attach=UserType.db;"), true) { }
        public MyDbContext(string connStr) : base(new SQLiteConnection(connStr), true) { }
        public DbSet<User> Users { get; set; }
        public DbSet<UserType> UserTypes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder) {

            modelBuilder
              .Entity<UserType>()
              .ToTable("UserType", "UserType");
        }
    }

    
    [DbConfigurationType(typeof(Devart.Data.SQLite.Entity.SQLiteEntityProviderServicesConfiguration))]
    class MyDbContextUser : DbContext {

        public MyDbContextUser(string connStr) : base(new SQLiteConnection(connStr), true) { }
        public DbSet<User> Users { get; set; }
    }


    [DbConfigurationType(typeof(Devart.Data.SQLite.Entity.SQLiteEntityProviderServicesConfiguration))]
    class MyDbContextUserType : DbContext {

        public MyDbContextUserType(string connStr) : base(new SQLiteConnection(connStr), true) { }
        public DbSet<UserType> UserTypes { get; set; }
    }
}

[bairog]

The modelBuilder.Entity<UserType>().ToTable("UserType", "UserType"); entry within the OnModelCreating method maps the UserType table to the UserType schema (UserType.db).

Thx, Code-First migrations are working correctly for me now.

Now we are looking closer to this approach. SQLite documentation says that

The number of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default. The maximum number of attached databases cannot be increased above 125.
The maximum number of attached databases can be lowered at run-time using the sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, size) interface

So is there a way to raise SQLITE_MAX_ATTACHED limit at a run-time?

[Shalex]

So is there a way to raise SQLITE_MAX_ATTACHED limit at a run-time?

There are two ways to change SQLITE_MAX_ATTACHED:

1) recompile sqlite3.dll with particular SQLITE_MAX_ATTACHED value. We may recompile a predefined sqlite3.dll with the SQLITE_MAX_ATTACHED value you need and send it to you (timeframe is one or two business days)

2) in runtime via sqlite3_limit API function. It is not supported at the moment. If you confirm your request, we can implement the corresponding support in dotConnect for SQLite (timeframe is one or two weeks)

[bairog]

2) in runtime via sqlite3_limit API function. It is not supported at the moment. If you confirm your request, we can implement the corresponding support in dotConnect for SQLite (timeframe is one or two weeks)

So it will be possible to raise the value, right? If yes - I confirm request for this feature. Thank you in advance
I'm asking because documentation says that

The maximum number of attached databases can be lowered at run-time using the sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, size) interface

[Shalex]

We will notify you when the feature is implemented.

[Shalex]

We have performed a deeper investigation of the question: there is no way to increase SQLITE_LIMIT_ATTACHED in runtime (API allows only to decrease the value). As we understood, the descrease has no use for you, so we may close your request for the feature (right?). If you are working with a standard SQLite engine (sqlite3.dll), we can compile it with the required value of SQLITE_LIMIT_ATTACHED and send it to you.

[bairog]

As we understood, the descrease has no use for you, so we may close your request for the feature (right?)

Correct, you may close request.

If you are working with a standard SQLite engine (sqlite3.dll), we can compile it with the required value of SQLITE_LIMIT_ATTACHED and send it to you.

For today 10 attached databases is sufficient. Looks like some time later we will need more than 10 - I will contact you.
Thank you.

[bairog]

I've tried to connect to "data source"+2 "attached" databases and failed to build correct connection string

Code: Select all

var sb = new SQLiteConnectionStringBuilder();
sb.DataSource = @"D:\Users";
sb.FailIfMissing = true;
sb.Attach = @"D:\UserTypes.db; D:\Messages.db";
connectionString = sb.ToString();

When trying to connect to database I receive an error (in fact that file exists)

Unable to open database file
unable to open database: D:\\Messages.db

I've tried @"D:\UserTypes.db, D:\Messages.db", @"D:\UserTypes.db D:\Messages.db" - nothing.
So how could I attach 2 or more databases?

I've updated my sample project (just copy *.db to D:\ and click "BeginTransaction()" button inside the right groupbox)

[Shalex]

Code: Select all

sb.Attach = @"D:\UserTypes.db; D:\Messages.db";

Please remove redundant space between database names:

Code: Select all

sb.Attach = @"D:\UserTypes.db;D:\Messages.db";

[bairog]

Thx. It's working for me now.