Backport pull request #11986 from jellyfin/release-10.9.z

Use only 1 write connection/DB

Original-merge: cc4563a4779ff7e86526b09f5ac5a2e7ec71e56b

Merged-by: crobibero <cody@robibe.ro>

Backported-by: Joshua M. Boniface <joshua@boniface.me>

4 files changed, 148 insertions, 19 deletions

diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
index a313d7cb3..4305e4b39 100644
--- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
+++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
@@ -4,6 +4,7 @@
 
 using System;
 using System.Collections.Generic;
+using System.Threading;
 using Jellyfin.Extensions;
 using Microsoft.Data.Sqlite;
 using Microsoft.Extensions.Logging;
@@ -13,6 +14,8 @@ namespace Emby.Server.Implementations.Data
     public abstract class BaseSqliteRepository : IDisposable
     {
         private bool _disposed = false;
+        private SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1);
+        private SqliteConnection _writeConnection;
 
         /// <summary>
         /// Initializes a new instance of the <see cref="BaseSqliteRepository"/> class.
@@ -98,9 +101,55 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        protected SqliteConnection GetConnection(bool readOnly = false)
+        protected ManagedConnection GetConnection(bool readOnly = false)
         {
-            var connection = new SqliteConnection($"Filename={DbFilePath}" + (readOnly ? ";Mode=ReadOnly" : string.Empty));
+            if (!readOnly)
+            {
+                _writeLock.Wait();
+                if (_writeConnection is not null)
+                {
+                    return new ManagedConnection(_writeConnection, _writeLock);
+                }
+
+                var writeConnection = new SqliteConnection($"Filename={DbFilePath};Pooling=False");
+                writeConnection.Open();
+
+                if (CacheSize.HasValue)
+                {
+                    writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value);
+                }
+
+                if (!string.IsNullOrWhiteSpace(LockingMode))
+                {
+                    writeConnection.Execute("PRAGMA locking_mode=" + LockingMode);
+                }
+
+                if (!string.IsNullOrWhiteSpace(JournalMode))
+                {
+                    writeConnection.Execute("PRAGMA journal_mode=" + JournalMode);
+                }
+
+                if (JournalSizeLimit.HasValue)
+                {
+                    writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value);
+                }
+
+                if (Synchronous.HasValue)
+                {
+                    writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value);
+                }
+
+                if (PageSize.HasValue)
+                {
+                    writeConnection.Execute("PRAGMA page_size=" + PageSize.Value);
+                }
+
+                writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore);
+
+                return new ManagedConnection(_writeConnection = writeConnection, _writeLock);
+            }
+
+            var connection = new SqliteConnection($"Filename={DbFilePath};Mode=ReadOnly");
             connection.Open();
 
             if (CacheSize.HasValue)
@@ -135,17 +184,17 @@ namespace Emby.Server.Implementations.Data
 
             connection.Execute("PRAGMA temp_store=" + (int)TempStore);
 
-            return connection;
+            return new ManagedConnection(connection, null);
         }
 
-        public SqliteCommand PrepareStatement(SqliteConnection connection, string sql)
+        public SqliteCommand PrepareStatement(ManagedConnection connection, string sql)
         {
             var command = connection.CreateCommand();
             command.CommandText = sql;
             return command;
         }
 
-        protected bool TableExists(SqliteConnection connection, string name)
+        protected bool TableExists(ManagedConnection connection, string name)
         {
             using var statement = PrepareStatement(connection, "select DISTINCT tbl_name from sqlite_master");
             foreach (var row in statement.ExecuteQuery())
@@ -159,7 +208,7 @@ namespace Emby.Server.Implementations.Data
             return false;
         }
 
-        protected List<string> GetColumnNames(SqliteConnection connection, string table)
+        protected List<string> GetColumnNames(ManagedConnection connection, string table)
         {
             var columnNames = new List<string>();
 
@@ -174,7 +223,7 @@ namespace Emby.Server.Implementations.Data
             return columnNames;
         }
 
-        protected void AddColumn(SqliteConnection connection, string table, string columnName, string type, List<string> existingColumnNames)
+        protected void AddColumn(ManagedConnection connection, string table, string columnName, string type, List<string> existingColumnNames)
         {
             if (existingColumnNames.Contains(columnName, StringComparison.OrdinalIgnoreCase))
             {
@@ -207,6 +256,24 @@ namespace Emby.Server.Implementations.Data
                 return;
             }
 
+            if (dispose)
+            {
+                _writeLock.Wait();
+                try
+                {
+                    _writeConnection.Dispose();
+                }
+                finally
+                {
+                    _writeLock.Release();
+                }
+
+                _writeLock.Dispose();
+            }
+
+            _writeConnection = null;
+            _writeLock = null;
+
             _disposed = true;
         }
     }
diff --git a/Emby.Server.Implementations/Data/ManagedConnection.cs b/Emby.Server.Implementations/Data/ManagedConnection.cs
new file mode 100644
index 000000000..860950b30
--- /dev/null
+++ b/Emby.Server.Implementations/Data/ManagedConnection.cs
@@ -0,0 +1,62 @@
+#pragma warning disable CS1591
+
+using System;
+using System.Collections.Generic;
+using System.Threading;
+using Microsoft.Data.Sqlite;
+
+namespace Emby.Server.Implementations.Data;
+
+public sealed class ManagedConnection : IDisposable
+{
+    private readonly SemaphoreSlim? _writeLock;
+
+    private SqliteConnection _db;
+
+    private bool _disposed = false;
+
+    public ManagedConnection(SqliteConnection db, SemaphoreSlim? writeLock)
+    {
+        _db = db;
+        _writeLock = writeLock;
+    }
+
+    public SqliteTransaction BeginTransaction()
+        => _db.BeginTransaction();
+
+    public SqliteCommand CreateCommand()
+        => _db.CreateCommand();
+
+    public void Execute(string commandText)
+        => _db.Execute(commandText);
+
+    public SqliteCommand PrepareStatement(string sql)
+        => _db.PrepareStatement(sql);
+
+    public IEnumerable<SqliteDataReader> Query(string commandText)
+        => _db.Query(commandText);
+
+    public void Dispose()
+    {
+        if (_disposed)
+        {
+            return;
+        }
+
+        if (_writeLock is null)
+        {
+            // Read connections are managed with an internal pool
+            _db.Dispose();
+        }
+        else
+        {
+            // Write lock is managed by BaseSqliteRepository
+            // Don't dispose here
+            _writeLock.Release();
+        }
+
+        _db = null!;
+
+        _disposed = true;
+    }
+}
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
index c2d31b474..d6a074195 100644
--- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
@@ -601,7 +601,7 @@ namespace Emby.Server.Implementations.Data
             transaction.Commit();
         }
 
-        private void SaveItemsInTransaction(SqliteConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples)
+        private void SaveItemsInTransaction(ManagedConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples)
         {
             using (var saveItemStatement = PrepareStatement(db, SaveItemCommandText))
             using (var deleteAncestorsStatement = PrepareStatement(db, "delete from AncestorIds where ItemId=@ItemId"))
@@ -1980,7 +1980,7 @@ namespace Emby.Server.Implementations.Data
             transaction.Commit();
         }
 
-        private void InsertChapters(Guid idBlob, IReadOnlyList<ChapterInfo> chapters, SqliteConnection db)
+        private void InsertChapters(Guid idBlob, IReadOnlyList<ChapterInfo> chapters, ManagedConnection db)
         {
             var startIndex = 0;
             var limit = 100;
@@ -4476,7 +4476,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
             transaction.Commit();
         }
 
-        private void ExecuteWithSingleParam(SqliteConnection db, string query, Guid value)
+        private void ExecuteWithSingleParam(ManagedConnection db, string query, Guid value)
         {
             using (var statement = PrepareStatement(db, query))
             {
@@ -4632,7 +4632,7 @@ AND Type = @InternalPersonType)");
             return whereClauses;
         }
 
-        private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, SqliteConnection db, SqliteCommand deleteAncestorsStatement)
+        private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, ManagedConnection db, SqliteCommand deleteAncestorsStatement)
         {
             if (itemId.IsEmpty())
             {
@@ -5148,7 +5148,7 @@ AND Type = @InternalPersonType)");
             return list;
         }
 
-        private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, SqliteConnection db)
+        private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, ManagedConnection db)
         {
             if (itemId.IsEmpty())
             {
@@ -5167,7 +5167,7 @@ AND Type = @InternalPersonType)");
             InsertItemValues(itemId, values, db);
         }
 
-        private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, SqliteConnection db)
+        private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, ManagedConnection db)
         {
             const int Limit = 100;
             var startIndex = 0;
@@ -5239,7 +5239,7 @@ AND Type = @InternalPersonType)");
             transaction.Commit();
         }
 
-        private void InsertPeople(Guid id, List<PersonInfo> people, SqliteConnection db)
+        private void InsertPeople(Guid id, List<PersonInfo> people, ManagedConnection db)
         {
             const int Limit = 100;
             var startIndex = 0;
@@ -5388,7 +5388,7 @@ AND Type = @InternalPersonType)");
             transaction.Commit();
         }
 
-        private void InsertMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, SqliteConnection db)
+        private void InsertMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, ManagedConnection db)
         {
             const int Limit = 10;
             var startIndex = 0;
@@ -5772,7 +5772,7 @@ AND Type = @InternalPersonType)");
         private void InsertMediaAttachments(
             Guid id,
             IReadOnlyList<MediaAttachment> attachments,
-            SqliteConnection db,
+            ManagedConnection db,
             CancellationToken cancellationToken)
         {
             const int InsertAtOnce = 10;
diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
index 66d009fd4..634eaf85e 100644
--- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
@@ -86,7 +86,7 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        private void ImportUserIds(SqliteConnection db, IEnumerable<User> users)
+        private void ImportUserIds(ManagedConnection db, IEnumerable<User> users)
         {
             var userIdsWithUserData = GetAllUserIdsWithUserData(db);
 
@@ -107,7 +107,7 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        private List<Guid> GetAllUserIdsWithUserData(SqliteConnection db)
+        private List<Guid> GetAllUserIdsWithUserData(ManagedConnection db)
         {
             var list = new List<Guid>();
 
@@ -176,7 +176,7 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        private static void SaveUserData(SqliteConnection db, long internalUserId, string key, UserItemData userData)
+        private static void SaveUserData(ManagedConnection db, long internalUserId, string key, UserItemData userData)
         {
             using (var statement = db.PrepareStatement("replace into UserDatas (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)"))
             {