如果有可能多个线程在SQLite数据库中执行插入/更新操作，我是否应该始终使用锁? [英] If there is a potential for multiple threads to do an insert / update into a SQLite database, should I always use lock?

问题描述

我有使用锁的这段代码:

            lock (l)
            {
                db2.BeginTransaction();
                try
                {
                    rc1 = db2.Execute(s);
                    db2.Commit();
                }
                catch (Exception ex)
                {
                    db2.Rollback();
                    Crashes.TrackError(ex,
                        new Dictionary<string, string> {
                            {"RunExecute", "Exception"},
                            {"sql", s },
                            {"Device Model", DeviceInfo.Model },
                        });
                    throw;
                }
            }

和其他没有的代码:

            db2.Insert(new QuizHistory()
            {
                QuizId = quiz,
                Cards  = (App.viewablePhrases ?? GetViewablePhrases(MO.Quiz)).Count,
                Score = score,
                UtcNow = (int)Math.Truncate(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1)).TotalSeconds),
                Viewed = 1,
                Deck   = Helpers.Deck.GetTitle()
            });

为了减少出现问题的机会，我是否应该在所有访问数据库的代码周围添加lock(l)?

解决方案

您一次只能提供对SQLite数据库的访问.

SQLite是基于文件的数据库，因此(使用一个SQLiteConnection)访问文件的多个线程会导致不可预测的行为和损坏错误.

如果您使用 SQLite-Net-PCL 库来处理SQLite数据库，那么您应该看一下在异步API 中，可同时访问SQLite数据库的多个线程.

有关SQLite数据库多线程支持的更多信息，请参考官方文档和

and other code that doesn't:

            db2.Insert(new QuizHistory()
            {
                QuizId = quiz,
                Cards  = (App.viewablePhrases ?? GetViewablePhrases(MO.Quiz)).Count,
                Score = score,
                UtcNow = (int)Math.Truncate(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1)).TotalSeconds),
                Viewed = 1,
                Deck   = Helpers.Deck.GetTitle()
            });

To reduce the chance of issues, should I be adding lock(l) around all the code that accesses the database?

解决方案

You must provide access to SQLite database only one thread at one time.

SQLite is a file-based database so accessing multiple threads to file (using one SQLiteConnection) causing unpredictable behaviour and corruption errors.

If you use SQLite-Net-PCL library for manipulation SQLite database then you should look at asynchronous API for multiple threads concurrent access to SQLite database.

Fore more information about SQLite database multithreading support please refer official documentation and related question

这篇关于如果有可能多个线程在SQLite数据库中执行插入/更新操作，我是否应该始终使用锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！