diff options
4 files changed, 86 insertions, 74 deletions
diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index 5f6518a14..bf8835846 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -129,7 +129,7 @@ namespace Emby.Server.Implementations.Activity var list = new List<ActivityLogEntry>(); var result = new QueryResult<ActivityLogEntry>(); - var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray())).ToList(); + var statements = PrepareAllSafe(db, statementTexts).ToList(); using (var statement = statements[0]) { diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 2fc721f83..b29309ef2 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -25,7 +25,7 @@ namespace Emby.Server.Implementations.Data protected TransactionMode TransactionMode { - get { return TransactionMode.Immediate; } + get { return TransactionMode.Deferred; } } protected TransactionMode ReadTransactionMode @@ -57,82 +57,86 @@ namespace Emby.Server.Implementations.Data protected SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false) { - if (!_versionLogged) + lock (WriteLock) { - _versionLogged = true; - Logger.Info("Sqlite version: " + SQLite3.Version); - Logger.Info("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray())); - } + if (!_versionLogged) + { + _versionLogged = true; + Logger.Info("Sqlite version: " + SQLite3.Version); + Logger.Info("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray())); + } - ConnectionFlags connectionFlags; + ConnectionFlags connectionFlags; - if (isReadOnly) - { - //Logger.Info("Opening read connection"); - //connectionFlags = ConnectionFlags.ReadOnly; - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; - } - else - { - //Logger.Info("Opening write connection"); - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; - } + if (isReadOnly) + { + //Logger.Info("Opening read connection"); + //connectionFlags = ConnectionFlags.ReadOnly; + connectionFlags = ConnectionFlags.Create; + connectionFlags |= ConnectionFlags.ReadWrite; + } + else + { + //Logger.Info("Opening write connection"); + connectionFlags = ConnectionFlags.Create; + connectionFlags |= ConnectionFlags.ReadWrite; + } - connectionFlags |= ConnectionFlags.SharedCached; - connectionFlags |= ConnectionFlags.NoMutex; + connectionFlags |= ConnectionFlags.SharedCached; + connectionFlags |= ConnectionFlags.NoMutex; - var db = SQLite3.Open(DbFilePath, connectionFlags, null); + var db = SQLite3.Open(DbFilePath, connectionFlags, null); - if (string.IsNullOrWhiteSpace(_defaultWal)) - { - _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); + if (string.IsNullOrWhiteSpace(_defaultWal)) + { + _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); - Logger.Info("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); - } + Logger.Info("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); + } - var queries = new List<string> + var queries = new List<string> { //"PRAGMA cache size=-10000" - //"PRAGMA read_uncommitted = true" + //"PRAGMA read_uncommitted = true", + "PRAGMA synchronous=Normal" }; - if (EnableTempStoreMemory) - { - queries.Add("PRAGMA temp_store = memory"); - } + if (EnableTempStoreMemory) + { + queries.Add("PRAGMA temp_store = memory"); + } - //var cacheSize = CacheSize; - //if (cacheSize.HasValue) - //{ + //var cacheSize = CacheSize; + //if (cacheSize.HasValue) + //{ - //} + //} - ////foreach (var query in queries) - ////{ - //// db.Execute(query); - ////} + ////foreach (var query in queries) + ////{ + //// db.Execute(query); + ////} - //Logger.Info("synchronous: " + db.Query("PRAGMA synchronous").SelectScalarString().First()); - //Logger.Info("temp_store: " + db.Query("PRAGMA temp_store").SelectScalarString().First()); + //Logger.Info("synchronous: " + db.Query("PRAGMA synchronous").SelectScalarString().First()); + //Logger.Info("temp_store: " + db.Query("PRAGMA temp_store").SelectScalarString().First()); - /*if (!string.Equals(_defaultWal, "wal", StringComparison.OrdinalIgnoreCase)) - { - queries.Add("PRAGMA journal_mode=WAL"); + /*if (!string.Equals(_defaultWal, "wal", StringComparison.OrdinalIgnoreCase)) + { + queries.Add("PRAGMA journal_mode=WAL"); - using (WriteLock.Write()) + using (WriteLock.Write()) + { + db.ExecuteAll(string.Join(";", queries.ToArray())); + } + } + else*/ + foreach (var query in queries) { - db.ExecuteAll(string.Join(";", queries.ToArray())); + db.Execute(query); } - } - else*/ - if (queries.Count > 0) - { - db.ExecuteAll(string.Join(";", queries.ToArray())); - } - return db; + return db; + } } public IStatement PrepareStatement(IDatabaseConnection connection, string sql) @@ -145,14 +149,14 @@ namespace Emby.Server.Implementations.Data return connection.PrepareStatement(sql); } - public List<IStatement> PrepareAll(IDatabaseConnection connection, string sql) + public List<IStatement> PrepareAll(IDatabaseConnection connection, IEnumerable<string> sql) { - return connection.PrepareAll(sql).ToList(); + return PrepareAllSafe(connection, sql); } - public List<IStatement> PrepareAllSafe(IDatabaseConnection connection, string sql) + public List<IStatement> PrepareAllSafe(IDatabaseConnection connection, IEnumerable<string> sql) { - return connection.PrepareAll(sql).ToList(); + return sql.Select(connection.PrepareStatement).ToList(); } protected void RunDefaultInitialization(IDatabaseConnection db) @@ -161,6 +165,7 @@ namespace Emby.Server.Implementations.Data { "PRAGMA journal_mode=WAL", "PRAGMA page_size=4096", + "PRAGMA synchronous=Normal" }; if (EnableTempStoreMemory) @@ -173,6 +178,7 @@ namespace Emby.Server.Implementations.Data } db.ExecuteAll(string.Join(";", queries.ToArray())); + Logger.Info("PRAGMA synchronous=" + db.Query("PRAGMA synchronous").SelectScalarString().First()); } protected virtual bool EnableTempStoreMemory diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 803ebeca0..f93819322 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -701,12 +701,12 @@ namespace Emby.Server.Implementations.Data { var requiresReset = false; - var statements = PrepareAll(db, string.Join(";", new string[] + var statements = PrepareAllSafe(db, new string[] { GetSaveItemCommandText(), "delete from AncestorIds where ItemId=@ItemId", "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)" - })).ToList(); + }).ToList(); using (var saveItemStatement = statements[0]) { @@ -1258,18 +1258,23 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection(true)) { - using (var statement = PrepareStatementSafe(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) + return connection.RunInTransaction(db => { - statement.TryBind("@guid", id); - - foreach (var row in statement.ExecuteQuery()) + using (var statement = PrepareStatementSafe(db, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) { - return GetItem(row); + statement.TryBind("@guid", id); + + foreach (var row in statement.ExecuteQuery()) + { + return GetItem(row); + } } - } + + return null; + + }, ReadTransactionMode); } } - return null; } private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader) @@ -2762,7 +2767,7 @@ namespace Emby.Server.Implementations.Data return connection.RunInTransaction(db => { var result = new QueryResult<BaseItem>(); - var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray())) + var statements = PrepareAllSafe(db, statementTexts) .ToList(); if (!isReturningZeroItems) @@ -3173,7 +3178,7 @@ namespace Emby.Server.Implementations.Data { var result = new QueryResult<Guid>(); - var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray())) + var statements = PrepareAllSafe(db, statementTexts) .ToList(); if (!isReturningZeroItems) @@ -5121,7 +5126,8 @@ namespace Emby.Server.Implementations.Data var list = new List<Tuple<BaseItem, ItemCounts>>(); var result = new QueryResult<Tuple<BaseItem, ItemCounts>>(); - var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray())).ToList(); + var statements = PrepareAllSafe(db, statementTexts) + .ToList(); if (!isReturningZeroItems) { diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index 7199f4f4d..a2d61873b 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -214,7 +214,7 @@ namespace Emby.Server.Implementations.Security statementTexts.Add(commandText); statementTexts.Add("select count (Id) from AccessTokens" + whereTextWithoutPaging); - var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray())) + var statements = PrepareAllSafe(db, statementTexts) .ToList(); using (var statement = statements[0]) |