Merge pull request #2340 from MediaBrowser/dev

Dev

4 files changed, 276 insertions, 243 deletions

diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
index 9e60a43aa..206422176 100644
--- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
+++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs
@@ -1,5 +1,6 @@
 using System;
 using System.Collections.Generic;
+using System.Globalization;
 using System.Threading;
 using System.Threading.Tasks;
 using MediaBrowser.Model.Logging;
@@ -25,7 +26,7 @@ namespace Emby.Server.Implementations.Data
 
         protected TransactionMode TransactionMode
         {
-            get { return TransactionMode.Immediate; }
+            get { return TransactionMode.Deferred; }
         }
 
         protected TransactionMode ReadTransactionMode
@@ -43,6 +44,8 @@ namespace Emby.Server.Implementations.Data
             //CheckOk(rc);
 
             rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MULTITHREAD, 1);
+            //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SINGLETHREAD, 1);
+            //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SERIALIZED, 1);
             //CheckOk(rc);
 
             rc = raw.sqlite3_enable_shared_cache(1);
@@ -53,84 +56,125 @@ namespace Emby.Server.Implementations.Data
         private static bool _versionLogged;
 
         private string _defaultWal;
+        protected ManagedConnection _connection;
 
-        protected SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false)
+        protected virtual bool EnableSingleConnection
         {
-            if (!_versionLogged)
-            {
-                _versionLogged = true;
-                Logger.Info("Sqlite version: " + SQLite3.Version);
-                Logger.Info("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray()));
-            }
-
-            ConnectionFlags connectionFlags;
+            get { return true; }
+        }
 
-            if (isReadOnly)
+        protected ManagedConnection CreateConnection(bool isReadOnly = false)
+        {
+            if (_connection != null)
             {
-                //Logger.Info("Opening read connection");
-                //connectionFlags = ConnectionFlags.ReadOnly;
-                connectionFlags = ConnectionFlags.Create;
-                connectionFlags |= ConnectionFlags.ReadWrite;
+                return _connection;
             }
-            else
+
+            lock (WriteLock)
             {
-                //Logger.Info("Opening write connection");
-                connectionFlags = ConnectionFlags.Create;
-                connectionFlags |= ConnectionFlags.ReadWrite;
-            }
+                if (!_versionLogged)
+                {
+                    _versionLogged = true;
+                    Logger.Info("Sqlite version: " + SQLite3.Version);
+                    Logger.Info("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray()));
+                }
 
-            connectionFlags |= ConnectionFlags.SharedCached;
-            connectionFlags |= ConnectionFlags.NoMutex;
+                ConnectionFlags connectionFlags;
 
-            var db = SQLite3.Open(DbFilePath, connectionFlags, null);
+                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 (string.IsNullOrWhiteSpace(_defaultWal))
-            {
-                _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First();
+                if (EnableSingleConnection)
+                {
+                    connectionFlags |= ConnectionFlags.PrivateCache;
+                }
+                else
+                {
+                    connectionFlags |= ConnectionFlags.SharedCached;
+                }
 
-                Logger.Info("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal);
-            }
+                connectionFlags |= ConnectionFlags.NoMutex;
 
-            var queries = new List<string>
-            {
-                //"PRAGMA cache size=-10000"
-            };
+                var db = SQLite3.Open(DbFilePath, connectionFlags, null);
 
-            if (EnableTempStoreMemory)
-            {
-                queries.Add("PRAGMA temp_store = memory");
-            }
+                if (string.IsNullOrWhiteSpace(_defaultWal))
+                {
+                    _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First();
 
-            //var cacheSize = CacheSize;
-            //if (cacheSize.HasValue)
-            //{
+                    Logger.Info("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal);
+                }
 
-            //}
+                var queries = new List<string>
+                {
+                    //"PRAGMA cache size=-10000"
+                    //"PRAGMA read_uncommitted = true",
+                    "PRAGMA synchronous=Normal"
+                };
 
-            ////foreach (var query in queries)
-            ////{
-            ////    db.Execute(query);
-            ////}
+                if (CacheSize.HasValue)
+                {
+                    queries.Add("PRAGMA cache_size=-" + CacheSize.Value.ToString(CultureInfo.InvariantCulture));
+                }
 
-            //Logger.Info("synchronous: " + db.Query("PRAGMA synchronous").SelectScalarString().First());
-            //Logger.Info("temp_store: " + db.Query("PRAGMA temp_store").SelectScalarString().First());
+                if (EnableTempStoreMemory)
+                {
+                    queries.Add("PRAGMA temp_store = memory");
+                }
 
-            /*if (!string.Equals(_defaultWal, "wal", StringComparison.OrdinalIgnoreCase))
-            {
-                queries.Add("PRAGMA journal_mode=WAL");
+                //var cacheSize = CacheSize;
+                //if (cacheSize.HasValue)
+                //{
 
-                using (WriteLock.Write())
+                //}
+
+                ////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());
+
+                /*if (!string.Equals(_defaultWal, "wal", StringComparison.OrdinalIgnoreCase))
                 {
-                    db.ExecuteAll(string.Join(";", queries.ToArray()));
+                    queries.Add("PRAGMA journal_mode=WAL");
+
+                    using (WriteLock.Write())
+                    {
+                        db.ExecuteAll(string.Join(";", queries.ToArray()));
+                    }
                 }
+                else*/
+                foreach (var query in queries)
+                {
+                    db.Execute(query);
+                }
+
+                _connection = new ManagedConnection(db, false);
+
+                return _connection;
             }
-            else*/
-            if (queries.Count > 0)
-            {
-                db.ExecuteAll(string.Join(";", queries.ToArray()));
-            }
+        }
+
+        public IStatement PrepareStatement(ManagedConnection connection, string sql)
+        {
+            return connection.PrepareStatement(sql);
+        }
 
-            return db;
+        public IStatement PrepareStatementSafe(ManagedConnection connection, string sql)
+        {
+            return connection.PrepareStatement(sql);
         }
 
         public IStatement PrepareStatement(IDatabaseConnection connection, string sql)
@@ -143,22 +187,23 @@ 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)
+        protected void RunDefaultInitialization(ManagedConnection db)
         {
             var queries = new List<string>
             {
                 "PRAGMA journal_mode=WAL",
                 "PRAGMA page_size=4096",
+                "PRAGMA synchronous=Normal"
             };
 
             if (EnableTempStoreMemory)
@@ -171,6 +216,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
@@ -238,6 +284,12 @@ namespace Emby.Server.Implementations.Data
                     {
                         using (WriteLock.Write())
                         {
+                            if (_connection != null)
+                            {
+                                _connection.Close();
+                                _connection = null;
+                            }
+
                             CloseConnection();
                         }
                     }
@@ -332,7 +384,7 @@ namespace Emby.Server.Implementations.Data
             //{
             //    return new DummyToken();
             //}
-            return new ReadLockToken(obj);
+            return new WriteLockToken(obj);
         }
         public static IDisposable Write(this ReaderWriterLockSlim obj)
         {
diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs
index 5b2549087..d6ad0ba8a 100644
--- a/Emby.Server.Implementations/Data/SqliteExtensions.cs
+++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs
@@ -129,7 +129,7 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        public static void Attach(IDatabaseConnection db, string path, string alias)
+        public static void Attach(ManagedConnection db, string path, string alias)
         {
             var commandText = string.Format("attach @path as {0};", alias);
 
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
index 14b81022d..5f2a314f9 100644
--- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
@@ -105,13 +105,7 @@ namespace Emby.Server.Implementations.Data
         {
             get
             {
-                var cacheSize = _config.Configuration.SqliteCacheSize;
-                if (cacheSize <= 0)
-                {
-                    cacheSize = Math.Min(Environment.ProcessorCount * 50000, 100000);
-                }
-
-                return 0 - cacheSize;
+                return 20000;
             }
         }
 
@@ -328,6 +322,8 @@ namespace Emby.Server.Implementations.Data
                 "drop table if exists Images",
                 "drop index if exists idx_Images",
                 "drop index if exists idx_TypeSeriesPresentationUniqueKey",
+                "drop index if exists idx_SeriesPresentationUniqueKey",
+                "drop index if exists idx_TypeSeriesPresentationUniqueKey2",
 
                 "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
                 "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
@@ -343,8 +339,9 @@ namespace Emby.Server.Implementations.Data
                 // series
                 "create index if not exists idx_TypeSeriesPresentationUniqueKey1 on TypedBaseItems(Type,SeriesPresentationUniqueKey,PresentationUniqueKey,SortName)",
 
-                // series next up
-                "create index if not exists idx_SeriesPresentationUniqueKey on TypedBaseItems(SeriesPresentationUniqueKey)",
+                // series counts
+                // seriesdateplayed sort order
+                "create index if not exists idx_TypeSeriesPresentationUniqueKey3 on TypedBaseItems(SeriesPresentationUniqueKey,Type,IsFolder,IsVirtualItem)",
 
                 // live tv programs
                 "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)",
@@ -373,9 +370,9 @@ namespace Emby.Server.Implementations.Data
                 //await Vacuum(_connection).ConfigureAwait(false);
             }
 
-            userDataRepo.Initialize(WriteLock);
+            userDataRepo.Initialize(WriteLock, _connection);
 
-            _shrinkMemoryTimer = _timerFactory.Create(OnShrinkMemoryTimerCallback, null, TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(30));
+            _shrinkMemoryTimer = _timerFactory.Create(OnShrinkMemoryTimerCallback, null, TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(15));
         }
 
         private void OnShrinkMemoryTimerCallback(object state)
@@ -698,12 +695,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])
             {
@@ -1264,9 +1261,10 @@ namespace Emby.Server.Implementations.Data
                             return GetItem(row);
                         }
                     }
+
+                    return null;
                 }
             }
-            return null;
         }
 
         private BaseItem GetItem(IReadOnlyList<IResultSetValue> reader)
@@ -2079,12 +2077,12 @@ namespace Emby.Server.Implementations.Data
                 throw new ArgumentNullException("id");
             }
 
-            var list = new List<ChapterInfo>();
-
             using (WriteLock.Read())
             {
                 using (var connection = CreateConnection(true))
                 {
+                    var list = new List<ChapterInfo>();
+
                     using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"))
                     {
                         statement.TryBind("@ItemId", id);
@@ -2094,10 +2092,10 @@ namespace Emby.Server.Implementations.Data
                             list.Add(GetChapter(row));
                         }
                     }
+
+                    return list;
                 }
             }
-
-            return list;
         }
 
         /// <summary>
@@ -2240,7 +2238,7 @@ namespace Emby.Server.Implementations.Data
 
             if (query.SimilarTo != null && query.User != null)
             {
-                return true;
+                //return true;
             }
 
             var sortingFields = query.SortBy.ToList();
@@ -2369,15 +2367,10 @@ namespace Emby.Server.Implementations.Data
                 builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 2 Else 0 End )");
                 builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 2 Else 0 End )");
 
-                //// genres
-                builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=2 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=2)) * 10)");
-
-                //// tags
-                builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=4 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=4)) * 10)");
-
-                builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=5 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=5)) * 10)");
+                //// genres, tags
+                builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type in (2,3,4,5) and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and Type in (2,3,4,5))) * 10)");
 
-                builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=3 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)");
+                //builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and Type=3 and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId and type=3)) * 3)");
 
                 //builder.Append("+ ((Select count(Name) from People where ItemId=Guid and Name in (select Name from People where ItemId=@SimilarItemId)) * 3)");
 
@@ -2475,8 +2468,6 @@ namespace Emby.Server.Implementations.Data
 
             //commandText += GetGroupBy(query);
 
-            int count = 0;
-
             using (WriteLock.Read())
             {
                 using (var connection = CreateConnection(true))
@@ -2493,14 +2484,13 @@ namespace Emby.Server.Implementations.Data
                         // Running this again will bind the params
                         GetWhereClauses(query, statement);
 
-                        count = statement.ExecuteQuery().SelectScalarInt().First();
+                        var count = statement.ExecuteQuery().SelectScalarInt().First();
+                        LogQueryTime("GetCount", commandText, now);
+                        return count;
                     }
                 }
 
-                LogQueryTime("GetCount", commandText, now);
             }
-
-            return count;
         }
 
         public List<BaseItem> GetItemList(InternalItemsQuery query)
@@ -2516,8 +2506,6 @@ namespace Emby.Server.Implementations.Data
 
             var now = DateTime.UtcNow;
 
-            var list = new List<BaseItem>();
-
             // Hack for right now since we currently don't support filtering out these duplicates within a query
             if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
             {
@@ -2558,53 +2546,59 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    using (var statement = PrepareStatementSafe(connection, commandText))
+                    return connection.RunInTransaction(db =>
                     {
-                        if (EnableJoinUserData(query))
+                        var list = new List<BaseItem>();
+
+                        using (var statement = PrepareStatementSafe(db, commandText))
                         {
-                            statement.TryBind("@UserId", query.User.Id);
-                        }
+                            if (EnableJoinUserData(query))
+                            {
+                                statement.TryBind("@UserId", query.User.Id);
+                            }
 
-                        BindSimilarParams(query, statement);
+                            BindSimilarParams(query, statement);
 
-                        // Running this again will bind the params
-                        GetWhereClauses(query, statement);
+                            // Running this again will bind the params
+                            GetWhereClauses(query, statement);
 
-                        foreach (var row in statement.ExecuteQuery())
-                        {
-                            var item = GetItem(row, query);
-                            if (item != null)
+                            foreach (var row in statement.ExecuteQuery())
                             {
-                                list.Add(item);
+                                var item = GetItem(row, query);
+                                if (item != null)
+                                {
+                                    list.Add(item);
+                                }
                             }
                         }
-                    }
-                }
 
-                LogQueryTime("GetItemList", commandText, now);
-            }
+                        // Hack for right now since we currently don't support filtering out these duplicates within a query
+                        if (query.EnableGroupByMetadataKey)
+                        {
+                            var limit = query.Limit ?? int.MaxValue;
+                            limit -= 4;
+                            var newList = new List<BaseItem>();
 
-            // Hack for right now since we currently don't support filtering out these duplicates within a query
-            if (query.EnableGroupByMetadataKey)
-            {
-                var limit = query.Limit ?? int.MaxValue;
-                limit -= 4;
-                var newList = new List<BaseItem>();
+                            foreach (var item in list)
+                            {
+                                AddItem(newList, item);
 
-                foreach (var item in list)
-                {
-                    AddItem(newList, item);
+                                if (newList.Count >= limit)
+                                {
+                                    break;
+                                }
+                            }
 
-                    if (newList.Count >= limit)
-                    {
-                        break;
-                    }
-                }
+                            list = newList;
+                        }
 
-                list = newList;
-            }
+                        LogQueryTime("GetItemList", commandText, now);
 
-            return list;
+                        return list;
+
+                    }, ReadTransactionMode);
+                }
+            }
         }
 
         private void AddItem(List<BaseItem> items, BaseItem newItem)
@@ -2642,7 +2636,7 @@ namespace Emby.Server.Implementations.Data
             var slowThreshold = 1000;
 
 #if DEBUG
-            slowThreshold = 50;
+            slowThreshold = 2;
 #endif
 
             if (elapsed >= slowThreshold)
@@ -2723,7 +2717,6 @@ namespace Emby.Server.Implementations.Data
                 }
             }
 
-            var result = new QueryResult<BaseItem>();
             var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
 
             var statementTexts = new List<string>();
@@ -2753,9 +2746,10 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    connection.RunInTransaction(db =>
+                    return connection.RunInTransaction(db =>
                     {
-                        var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray()))
+                        var result = new QueryResult<BaseItem>();
+                        var statements = PrepareAllSafe(db, statementTexts)
                             .ToList();
 
                         if (!isReturningZeroItems)
@@ -2801,12 +2795,12 @@ namespace Emby.Server.Implementations.Data
                             }
                         }
 
-                    }, ReadTransactionMode);
+                        LogQueryTime("GetItems", commandText, now);
 
-                    LogQueryTime("GetItems", commandText, now);
+                        result.Items = list.ToArray();
+                        return result;
 
-                    result.Items = list.ToArray();
-                    return result;
+                    }, ReadTransactionMode);
                 }
             }
         }
@@ -2967,12 +2961,12 @@ namespace Emby.Server.Implementations.Data
                 }
             }
 
-            var list = new List<Guid>();
-
             using (WriteLock.Read())
             {
                 using (var connection = CreateConnection(true))
                 {
+                    var list = new List<Guid>();
+
                     using (var statement = PrepareStatementSafe(connection, commandText))
                     {
                         if (EnableJoinUserData(query))
@@ -2990,11 +2984,11 @@ namespace Emby.Server.Implementations.Data
                             list.Add(row[0].ReadGuid());
                         }
                     }
-                }
 
-                LogQueryTime("GetItemList", commandText, now);
+                    LogQueryTime("GetItemList", commandText, now);
 
-                return list;
+                    return list;
+                }
             }
         }
 
@@ -3158,11 +3152,11 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    var result = new QueryResult<Guid>();
-
-                    connection.RunInTransaction(db =>
+                    return connection.RunInTransaction(db =>
                     {
-                        var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray()))
+                        var result = new QueryResult<Guid>();
+
+                        var statements = PrepareAllSafe(db, statementTexts)
                             .ToList();
 
                         if (!isReturningZeroItems)
@@ -3204,12 +3198,12 @@ namespace Emby.Server.Implementations.Data
                             }
                         }
 
-                    }, ReadTransactionMode);
+                        LogQueryTime("GetItemIds", commandText, now);
 
-                    LogQueryTime("GetItemIds", commandText, now);
+                        result.Items = list.ToArray();
+                        return result;
 
-                    result.Items = list.ToArray();
-                    return result;
+                    }, ReadTransactionMode);
                 }
             }
         }
@@ -4658,26 +4652,23 @@ namespace Emby.Server.Implementations.Data
 
             commandText += " order by ListOrder";
 
-            var list = new List<string>();
             using (WriteLock.Read())
             {
                 using (var connection = CreateConnection(true))
                 {
-                    connection.RunInTransaction(db =>
+                    var list = new List<string>();
+                    using (var statement = PrepareStatementSafe(connection, commandText))
                     {
-                        using (var statement = PrepareStatementSafe(db, commandText))
-                        {
-                            // Run this again to bind the params
-                            GetPeopleWhereClauses(query, statement);
+                        // Run this again to bind the params
+                        GetPeopleWhereClauses(query, statement);
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(row.GetString(0));
-                            }
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            list.Add(row.GetString(0));
                         }
-                    }, ReadTransactionMode);
+                    }
+                    return list;
                 }
-                return list;
             }
         }
 
@@ -4701,29 +4692,26 @@ namespace Emby.Server.Implementations.Data
 
             commandText += " order by ListOrder";
 
-            var list = new List<PersonInfo>();
-
             using (WriteLock.Read())
             {
                 using (var connection = CreateConnection(true))
                 {
-                    connection.RunInTransaction(db =>
+                    var list = new List<PersonInfo>();
+
+                    using (var statement = PrepareStatementSafe(connection, commandText))
                     {
-                        using (var statement = PrepareStatementSafe(db, commandText))
-                        {
-                            // Run this again to bind the params
-                            GetPeopleWhereClauses(query, statement);
+                        // Run this again to bind the params
+                        GetPeopleWhereClauses(query, statement);
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(GetPerson(row));
-                            }
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            list.Add(GetPerson(row));
                         }
-                    }, ReadTransactionMode);
+                    }
+
+                    return list;
                 }
             }
-
-            return list;
         }
 
         private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, IStatement statement)
@@ -4904,8 +4892,6 @@ namespace Emby.Server.Implementations.Data
                 ("Type=" + itemValueTypes[0].ToString(CultureInfo.InvariantCulture)) :
                 ("Type in (" + string.Join(",", itemValueTypes.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray()) + ")");
 
-            var list = new List<string>();
-
             var commandText = "Select Value From ItemValues where " + typeClause;
 
             if (withItemTypes.Count > 0)
@@ -4925,24 +4911,24 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    connection.RunInTransaction(db =>
+                    var list = new List<string>();
+
+                    using (var statement = PrepareStatementSafe(connection, commandText))
                     {
-                        using (var statement = PrepareStatementSafe(db, commandText))
+                        foreach (var row in statement.ExecuteQuery())
                         {
-                            foreach (var row in statement.ExecuteQuery())
+                            if (!row.IsDBNull(0))
                             {
-                                if (!row.IsDBNull(0))
-                                {
-                                    list.Add(row.GetString(0));
-                                }
+                                list.Add(row.GetString(0));
                             }
                         }
-                    }, ReadTransactionMode);
+                    }
+
+                    LogQueryTime("GetItemValueNames", commandText, now);
+
+                    return list;
                 }
             }
-            LogQueryTime("GetItemValueNames", commandText, now);
-
-            return list;
         }
 
         private QueryResult<Tuple<BaseItem, ItemCounts>> GetItemValues(InternalItemsQuery query, int[] itemValueTypes, string returnType)
@@ -5086,9 +5072,6 @@ namespace Emby.Server.Implementations.Data
 
             var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
 
-            var list = new List<Tuple<BaseItem, ItemCounts>>();
-            var result = new QueryResult<Tuple<BaseItem, ItemCounts>>();
-
             var statementTexts = new List<string>();
             if (!isReturningZeroItems)
             {
@@ -5107,9 +5090,13 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    connection.RunInTransaction(db =>
+                    return connection.RunInTransaction(db =>
                     {
-                        var statements = PrepareAllSafe(db, string.Join(";", statementTexts.ToArray())).ToList();
+                        var list = new List<Tuple<BaseItem, ItemCounts>>();
+                        var result = new QueryResult<Tuple<BaseItem, ItemCounts>>();
+
+                        var statements = PrepareAllSafe(db, statementTexts)
+                            .ToList();
 
                         if (!isReturningZeroItems)
                         {
@@ -5172,17 +5159,18 @@ namespace Emby.Server.Implementations.Data
                                 LogQueryTime("GetItemValues", commandText, now);
                             }
                         }
+
+                        if (result.TotalRecordCount == 0)
+                        {
+                            result.TotalRecordCount = list.Count;
+                        }
+                        result.Items = list.ToArray();
+
+                        return result;
+
                     }, ReadTransactionMode);
                 }
             }
-
-            if (result.TotalRecordCount == 0)
-            {
-                result.TotalRecordCount = list.Count;
-            }
-            result.Items = list.ToArray();
-
-            return result;
         }
 
         private ItemCounts GetItemCounts(IReadOnlyList<IResultSetValue> reader, int countStartColumn, List<string> typesToCount)
@@ -5395,8 +5383,6 @@ namespace Emby.Server.Implementations.Data
                 throw new ArgumentNullException("query");
             }
 
-            var list = new List<MediaStream>();
-
             var cmdText = "select " + string.Join(",", _mediaStreamSaveColumns) + " from mediastreams where";
 
             cmdText += " ItemId=@ItemId";
@@ -5417,32 +5403,31 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    connection.RunInTransaction(db =>
+                    var list = new List<MediaStream>();
+
+                    using (var statement = PrepareStatementSafe(connection, cmdText))
                     {
-                        using (var statement = PrepareStatementSafe(db, cmdText))
-                        {
-                            statement.TryBind("@ItemId", query.ItemId.ToGuidParamValue());
+                        statement.TryBind("@ItemId", query.ItemId.ToGuidParamValue());
 
-                            if (query.Type.HasValue)
-                            {
-                                statement.TryBind("@StreamType", query.Type.Value.ToString());
-                            }
+                        if (query.Type.HasValue)
+                        {
+                            statement.TryBind("@StreamType", query.Type.Value.ToString());
+                        }
 
-                            if (query.Index.HasValue)
-                            {
-                                statement.TryBind("@StreamIndex", query.Index.Value);
-                            }
+                        if (query.Index.HasValue)
+                        {
+                            statement.TryBind("@StreamIndex", query.Index.Value);
+                        }
 
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                list.Add(GetMediaStream(row));
-                            }
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            list.Add(GetMediaStream(row));
                         }
-                    }, ReadTransactionMode);
+                    }
+
+                    return list;
                 }
             }
-
-            return list;
         }
 
         public async Task SaveMediaStreams(Guid id, List<MediaStream> streams, CancellationToken cancellationToken)
diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
index 7afb5720e..2e39b038a 100644
--- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs
@@ -42,8 +42,10 @@ namespace Emby.Server.Implementations.Data
         /// Opens the connection to the database
         /// </summary>
         /// <returns>Task.</returns>
-        public void Initialize(ReaderWriterLockSlim writeLock)
+        public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection)
         {
+            _connection = managedConnection;
+
             WriteLock.Dispose();
             WriteLock = writeLock;
 
@@ -90,7 +92,7 @@ namespace Emby.Server.Implementations.Data
             }
         }
 
-        private void ImportUserDataIfNeeded(IDatabaseConnection connection)
+        private void ImportUserDataIfNeeded(ManagedConnection connection)
         {
             if (!_fileSystem.FileExists(_importFile))
             {
@@ -117,7 +119,7 @@ namespace Emby.Server.Implementations.Data
             }, TransactionMode);
         }
 
-        private void ImportUserData(IDatabaseConnection connection, string file)
+        private void ImportUserData(ManagedConnection connection, string file)
         {
             SqliteExtensions.Attach(connection, file, "UserDataBackup");
 
@@ -300,24 +302,18 @@ namespace Emby.Server.Implementations.Data
             {
                 using (var connection = CreateConnection(true))
                 {
-                    UserItemData result = null;
-
-                    connection.RunInTransaction(db =>
+                    using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId"))
                     {
-                        using (var statement = db.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key =@Key and userId=@UserId"))
+                        statement.TryBind("@UserId", userId.ToGuidParamValue());
+                        statement.TryBind("@Key", key);
+
+                        foreach (var row in statement.ExecuteQuery())
                         {
-                            statement.TryBind("@UserId", userId.ToGuidParamValue());
-                            statement.TryBind("@Key", key);
-
-                            foreach (var row in statement.ExecuteQuery())
-                            {
-                                result = ReadRow(row);
-                                break;
-                            }
+                            return ReadRow(row);
                         }
-                    }, ReadTransactionMode);
+                    }
 
-                    return result;
+                    return null;
                 }
             }
         }