EntityFramework Core併發遷移解決方案

贾光辉發表於2024-09-19

場景

目前一個專案中資料持久化採用EF Core + MySQL，使用CodeFirst模式開發，並且對資料進行了分庫，執行期間庫也會動態的增加，按照目前顆粒度分完之後，大概有一兩百個庫，每個庫的資料都是相互隔離的。
借鑑了Github上一個開源的倉庫 arch/UnitOfWork 實現UnitOfWork，核心操作就是每個api請求的時候帶上庫名，在執行CRUD之前先將DbContext切換到目標資料庫，我們在切換資料庫的時候加了一些操作，如檢查資料庫是否已建立、檢查連線是否可用、判斷是否需要 表結構遷移 等

/// <summary>
/// 切換資料庫 這要求資料庫在同一臺機器上 注意:這隻適用於MySQL。
/// </summary>
/// <param name="database">目標資料庫</param>
public void ChangeDatabase(string database)
{
    // 檢查連線
    ......

    // 檢查資料庫是否建立
    ......

    var connection = _context.Database.GetDbConnection();
    if (connection.State.HasFlag(ConnectionState.Open))
    {
        connection.ChangeDatabase(database);
    }
    else
    {
        var connectionString = Regex.Replace(connection.ConnectionString.Replace(" ", ""), @"(?<=[Dd]atabase=)\w+(?=;)", database, RegexOptions.Singleline);
        connection.ConnectionString = connectionString;
    }

    // 判斷是否需要執行表結構遷移
    if(_context..Database.GetPendingMigrations().Any())
    {
        //自定義的遷移的一些邏輯
        _context.Database.Migrate(_context);
    }
}

但是當多個操作同時對一個庫進行Migrate的時候，就會出現問題，比如“新增一張表”的操作已經被第一個遷移執行過了，第二個執行的遷移並不知道已經執行過了Migrate，就會報錯表已存在。
於是考慮在執行Migrate的時候，加入一個鎖的機制，對當前資料庫執行Migrate之前先獲取鎖，然後再來決定接下來的操作。由於這邊有的服務無法訪問Redis，這裡使用資料庫來實現鎖的機制，當然用Redis來實現更好，加入鎖的機制只是一種解決問題的思路。

利用資料庫實現遷移鎖

1. 新增 `MigrationLocks` 表來實現遷移鎖

鎖的操作不依賴DbContext例項

在執行Migrate之前，嘗試獲取一個鎖，在獲取鎖之前，如果表不存在則建立

CREATE TABLE IF NOT EXISTS MigrationLocks (
    LockName VARCHAR(255) PRIMARY KEY,
    LockedAt DATETIME NOT NULL
);

成功往表中插入一條記錄，視為獲取鎖成功，主鍵為需要遷移的庫的名稱
```
INSERT INTO MigrationLocks (LockName, LockedAt) VALUES (@database, NOW());
```
遷移完成後，刪除這條記錄，視為釋放鎖成功；
```
DELETE FROM MigrationLocks WHERE LockName = @database;
```
為防止 “死鎖” 發生，每次嘗試獲取鎖之前，會對鎖的狀態進行檢查，釋放超過5分鐘的鎖（正常來說，上一個遷移的執行時間不會超過5分鐘）。
```
SELECT COUNT(*) FROM MigrationLocks WHERE LockName = @database AND LockedAt > NOW() - INTERVAL 5 MINUTE;
```

2. 封裝一下MigrateLock的實現

/// <summary>
/// 遷移鎖
/// </summary>
public interface IMigrateLock
{
    /// <summary>
    /// 嘗試獲取鎖
    /// </summary>
    /// <param name="connection"></param>
    /// <returns></returns>
    bool TryAcquireLock(IDbConnection connection);

    /// <summary>
    /// 嘗試獲取鎖
    /// </summary>
    /// <param name="connection"></param>
    /// <returns></returns>
    Task<bool> TryAcquireLockAsync(IDbConnection connection);

    /// <summary>
    /// 釋放鎖
    /// </summary>
    void ReleaseLock(IDbConnection connection);

    /// <summary>
    /// 釋放鎖
    /// </summary>
    /// <returns></returns>
    Task ReleaseLockAsync(IDbConnection connection);
}

/// <summary>
/// 遷移鎖
/// </summary>
public class MigrateLock : IMigrateLock
{
    private readonly ILogger<MigrateLock> _logger;

    public MigrateLock(ILogger<MigrateLock> logger)
    {
        _logger = logger;
    }

    private const string CreateTableSql = @"
        CREATE TABLE IF NOT EXISTS MigrationLocks (
            LockName VARCHAR(255) PRIMARY KEY,
            LockedAt DATETIME NOT NULL
        );";

    private const string CheckLockedSql = "SELECT COUNT(*) FROM MigrationLocks WHERE LockName = @database AND LockedAt > NOW() - INTERVAL 5 MINUTE;";

    private const string AcquireLockSql = "INSERT INTO MigrationLocks (LockName, LockedAt) VALUES (@database, NOW());";

    private const string ReleaseLockSql = "DELETE FROM MigrationLocks WHERE LockName = @database;";

    /// <summary>
    /// 嘗試獲取鎖
    /// </summary>
    /// <param name="connection"></param>
    /// <returns></returns>
    public bool TryAcquireLock(IDbConnection connection)
    {
        try  
        {
            CheckLocked(connection);

            var result = connection.Execute(AcquireLockSql, new { database = connection.Database });
            if (result == 1)
            {
                _logger.LogInformation("Lock acquired: {LockName}", connection.Database);

                return true;
            }

            _logger.LogWarning("Failed to acquire lock: {LockName}", connection.Database);

            return false;
        }
        catch (Exception ex)
        {
            if (ex.Message.StartsWith("Duplicate"))
            {
                _logger.LogWarning("Failed acquiring lock due to duplicate entry: {LockName}", connection.Database);
            }
            else
            {
                _logger.LogError(ex, "Error acquiring lock: {LockName}", connection.Database);
            }

            return false;
        }
    }

    /// <summary>
    /// 嘗試獲取鎖
    /// </summary>
    /// <param name="connection"></param>
    /// <returns></returns>
    public async Task<bool> TryAcquireLockAsync(IDbConnection connection)
    {
        try
        {
            await CheckLockedAsync(connection);

            var result = await connection.ExecuteAsync(AcquireLockSql, new { database = connection.Database });
            if (result == 1)
            {
                _logger.LogInformation("Lock acquired: {LockName}", connection.Database);

                return true;
            }

            _logger.LogWarning("Failed to acquire lock: {LockName}", connection.Database);

            return false;
        }
        catch (Exception ex)
        {
            if (ex.Message.StartsWith("Duplicate"))
            {
                _logger.LogWarning("Failed acquiring lock due to duplicate entry: {LockName}", connection.Database);
            }
            else
            {
                _logger.LogError(ex, "Error acquiring lock: {LockName}", connection.Database);
            }

            return false;
        }
    }

    /// <summary>
    /// 釋放鎖
    /// </summary>
    public void ReleaseLock(IDbConnection connection)
    {
        try
        {
            connection.ExecuteAsync(ReleaseLockSql, new { database = connection.Database });
            _logger.LogInformation("Lock released: {LockName}", connection.Database);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error releasing lock: {LockName}", connection.Database);
        }
    }

    /// <summary>
    /// 釋放鎖
    /// </summary>
    public async Task ReleaseLockAsync(IDbConnection connection)
    {
        try
        {
            await connection.ExecuteAsync(ReleaseLockSql, new { database = connection.Database });
            _logger.LogInformation("Lock released: {LockName}", connection.Database);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error releasing lock: {LockName}", connection.Database);
        }
    }

    /// <summary>
    /// 檢查鎖
    /// </summary>
    private void CheckLocked(IDbConnection connection)
    {
        connection.Execute(CreateTableSql); 

        var databaseParam = new
        {
            database = connection.Database
        };

        var lockExists = connection.QueryFirstOrDefault<int>(CheckLockedSql, databaseParam);
        if (lockExists <= 0)
        {
            return;
        }

        _logger.LogWarning("Lock exists and is older than 5 minutes. Releasing old lock.");
        connection.Execute(ReleaseLockSql, databaseParam);
    }

    /// <summary>
    /// 檢查鎖
    /// </summary>
    private async Task CheckLockedAsync(IDbConnection connection)
    {
        await connection.ExecuteAsync(CreateTableSql);
         
        var databaseParam = new
        {
            database = connection.Database
        };

        var lockExists = await connection.QueryFirstOrDefaultAsync<int>(CheckLockedSql, databaseParam);
        if (lockExists <= 0)
        {
            return;
        }

        _logger.LogWarning("Lock exists and is older than 5 minutes. Releasing old lock.");
        await connection.ExecuteAsync(ReleaseLockSql, databaseParam);
    }
}

3. 封裝一下MigrateExecutor的實現

/// <summary>
/// 資料庫遷移執行器
/// </summary>
public interface IMigrateExcutor
{
    /// <summary>
    /// 執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    void Migrate(DbContext dbContext);

    /// <summary>
    /// 執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <returns></returns>
    Task MigrateAsync(DbContext dbContext);

    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    void ConcurrentMigrate(DbContext dbContext, bool wait = true);
     
    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    /// <returns></returns>
    Task ConcurrentMigrateAsync(DbContext dbContext, bool wait = true);

    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="connection"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    void ConcurrentMigrate(DbContext dbContext, IDbConnection connection, bool wait = true);

    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="connection"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    Task ConcurrentMigrateAsync(DbContext dbContext, IDbConnection connection, bool wait = true);
}

/// <summary>
/// 資料庫遷移執行器
/// </summary>
public class MigrateExcutor : IMigrateExcutor
{
    private readonly IMigrateLock _migrateLock;
    private readonly ILogger<MigrateExcutor> _logger;

    public MigrateExcutor(
        IMigrateLock migrateLock,
        ILogger<MigrateExcutor> logger)
    {
        _migrateLock = migrateLock;
        _logger = logger;
    }

    /// <summary>
    /// 執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <returns></returns>
    public void Migrate(DbContext dbContext)
    {
        try
        {
            if (dbContext.Database.GetPendingMigrations().Any())
            {
                dbContext.Database.Migrate();
            }
        }
        catch (Exception e)
        {
            _logger.LogError(e, "Migration failed");

            HandleError(dbContext, e);
        }
    }

    /// <summary>
    /// 執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <returns></returns>
    public async Task MigrateAsync(DbContext dbContext)
    {
        try
        {
            if ((await dbContext.Database.GetPendingMigrationsAsync()).Any())
            {
                await dbContext.Database.MigrateAsync();
            }
        }
        catch (Exception e)
        {
            _logger.LogError(e, "Migration failed");

            await HandleErrorAsync(dbContext, e);
        }
    }

    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    /// <returns></returns>
    public void ConcurrentMigrate(DbContext dbContext, bool wait = true)
    {
        if (!dbContext.Database.GetPendingMigrations().Any())
        {
            return;
        }

        using var connection = MySqlConnectionHelper.CreateConnection(dbContext.Database.GetDbConnection().Database);

        ConcurrentMigrate(dbContext, connection, wait);
    }

    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    /// <returns></returns>
    public async Task ConcurrentMigrateAsync(DbContext dbContext, bool wait = true)
    {
        if ((await dbContext.Database.GetPendingMigrationsAsync()).Any())
        {
            return;
        }

        await using var connection = await MySqlConnectionHelper.CreateConnectionAsync(dbContext.Database.GetDbConnection().Database);

        await ConcurrentMigrateAsync(dbContext, connection, wait);
    }

    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="connection"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    public void ConcurrentMigrate(DbContext dbContext, IDbConnection connection, bool wait = true)
    {
        if (!dbContext.Database.GetPendingMigrations().Any())
        {
            return;
        }

        while (true)
        {
            if (_migrateLock.TryAcquireLock(connection))
            {
                try
                {
                    Migrate(dbContext);

                    break;
                }
                finally
                {
                    _migrateLock.ReleaseLock(connection);
                }
            }

            if (wait)
            {
                _logger.LogWarning("Migration is locked, wait for 2 seconds");
                Thread.Sleep(20000);

                continue;
            }

            _logger.LogInformation("Migration is locked, skip");
        }
    }

    /// <summary>
    /// 併發場景執行遷移
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="connection"></param>
    /// <param name="wait">是否等待至正在進行中的遷移完成</param>
    public async Task ConcurrentMigrateAsync(DbContext dbContext, IDbConnection connection, bool wait = true)
    {
        if ((await dbContext.Database.GetPendingMigrationsAsync()).Any())
        {
            return;
        }

        while (true)
        {
            if (await _migrateLock.TryAcquireLockAsync(connection))
            {
                try
                {
                    await MigrateAsync(dbContext);
                    break;
                }
                finally
                {
                    await _migrateLock.ReleaseLockAsync(connection);
                }
            }

            if (wait)
            {
                _logger.LogWarning("Migration is locked, wait for 2 seconds");
                Thread.Sleep(20000);

                continue;
            }

            _logger.LogInformation("Migration is locked, skip");

            break;
        }
    }

    private void HandleError(DbContext dbContext, Exception e)
    {
        var needChangeList = dbContext.Database.GetPendingMigrations().ToList();
        var allChangeList = dbContext.Database.GetMigrations().ToList();
        var hasChangeList = dbContext.Database.GetAppliedMigrations().ToList();

        if (needChangeList.Count + hasChangeList.Count > allChangeList.Count)
        {
            int errIndex = allChangeList.Count - needChangeList.Count;

            if (hasChangeList.Count - 1 == errIndex && hasChangeList[errIndex] != needChangeList[0])
            {
                int index = needChangeList[0].IndexOf("_", StringComparison.Ordinal);
                string errSuffix = needChangeList[0].Substring(index, needChangeList[0].Length - index);
                if (hasChangeList[errIndex].EndsWith(errSuffix))
                {
                    dbContext.Database.ExecuteSqlRaw($"Update __EFMigrationsHistory set MigrationId = '{needChangeList[0]}' where MigrationId = '{hasChangeList[errIndex]}'");
                    dbContext.Database.Migrate();
                }
                else
                {
                    throw e;
                }
            }
            else
            {
                throw e;
            }
        }
        else
        {
            throw e;
        }

        _logger.LogInformation("Migration failed, but success on second try.");
    }

    private async Task HandleErrorAsync(DbContext dbContext, Exception e)
    {
        var needChangeList = (await dbContext.Database.GetPendingMigrationsAsync()).ToList();
        var allChangeList = dbContext.Database.GetMigrations().ToList();
        var hasChangeList = (await dbContext.Database.GetAppliedMigrationsAsync()).ToList();

        if (needChangeList.Count + hasChangeList.Count > allChangeList.Count)
        {
            int errIndex = allChangeList.Count - needChangeList.Count;

            if (hasChangeList.Count - 1 == errIndex && hasChangeList[errIndex] != needChangeList[0])
            {
                int index = needChangeList[0].IndexOf("_", StringComparison.Ordinal);
                string errSuffix = needChangeList[0].Substring(index, needChangeList[0].Length - index);
                if (hasChangeList[errIndex].EndsWith(errSuffix))
                {
                    await dbContext.Database.ExecuteSqlRawAsync($"Update __EFMigrationsHistory set MigrationId = '{needChangeList[0]}' where MigrationId = '{hasChangeList[errIndex]}'");
                    await dbContext.Database.MigrateAsync();
                }
                else
                {
                    throw e;
                }
            }
            else
            {
                throw e;
            }
        }
        else
        {
            throw e;
        }

        _logger.LogInformation("Migration failed, but success on second try.");
    }
}

iOS 資料庫升級資料遷移解決方案
2019-04-04
iOS資料庫
阿里雲釋出遷移解決方案，“遷移上雲”這件事有專業的人做
2018-11-13
阿里
EntityFramework Core 遷移忽略主外來鍵關係
2020-06-30
Framework
XTTS系列之一：U2L遷移解決方案之XTTS的使用
2019-07-28
TTS
EntityFramework Core健康檢查
2020-12-02
Framework
EntityFramework Core 5.0 VS SQLBulkCopy
2020-12-08
FrameworkSQL
[翻譯]EntityFramework Core 2.2 釋出
2018-12-05
Framework
EntityFramework Core筆記：入門（1）
2018-05-27
Framework筆記
EF Core 遷移過程遇到EF Core tools version版本不相符的解決方案
2018-11-25
mysql 高併發 select update 併發更新問題解決方案
2020-10-28
MySql
EntityFramework Core如何對映動態模型？
2021-01-24
Framework模型
Elasticsearch——併發衝突以及解決方案
2019-02-15
Elasticsearch
高併發解決方案orleans實踐
2023-01-10
高併發和大流量解決方案
2022-12-12
高併發解決方案詳解(9大常見解決方案)
2024-02-29
EntityFramework Core筆記：儲存資料（4）
2018-05-31
Framework筆記
EntityFramework Core筆記：查詢資料（3）
2018-05-30
Framework筆記
高併發大容量NoSQL解決方案探索
2018-05-14
SQL
EntityFramework Core上下文例項池原理分析
2020-11-08
Framework
海量資料和高併發的解決方案
2019-04-02
PHP 併發場景的幾種解決方案
2019-06-27
PHP
高併發下丟失更新的解決方案
2022-11-23
PHP高併發和大流量的解決方案
2021-04-25
PHP
MSSQL Server 遷移至 ORACLE解決方案
2023-03-02
SQLServerOracle
突破瀏覽器域名併發限制的解決方案
2019-12-31
瀏覽器
高併發下的介面冪等性解決方案！
2022-12-08
多執行緒併發同步問題及解決方案
2019-07-20
執行緒
PHP高併發商品秒殺問題的解決方案
2022-07-05
PHP
EntityFramework Core筆記：表結構及資料基本操作（2）
2018-05-27
Framework筆記
Entity Framework Core中的併發處理
2024-05-13
Framework
談談高併發系統的一些解決方案
2022-04-05
ASP.NET Core Identity自定義資料庫結構和完全使用Dapper而非EntityFramework Core
2019-08-03
ASP.NETIDE資料庫APPFramework
分散式系統設計中的併發訪問解決方案
2023-05-18
分散式
高併發業務場景下的秒殺解決方案 (初探)
2019-10-15
架構與思維：高併發下冪等性解決方案
2021-12-31
架構
Entityframework Migrations
2018-12-29
Framework
摩杜雲遷移全週期解決方案：助力企業加速遷移
2021-05-31
高併發設計技術方案
2024-08-07

EntityFramework Core併發遷移解決方案

場景

利用資料庫實現遷移鎖

1. 新增 MigrationLocks 表來實現遷移鎖

2. 封裝一下MigrateLock的實現

3. 封裝一下MigrateExecutor的實現

相關文章

1. 新增 `MigrationLocks` 表來實現遷移鎖