SQLite的"数据库被锁定"错误多线程应用程序 [英] SQLite "Database is locked" error in multithreads application
问题描述
有一个多线程的应用程序,这与大型数据库文件(> 600 MB)的作品。 数据库已被锁定的问题开始时,我添加BLOB数据,并开始与每个请求BLOB数据的> 30 KB的操作。我认为,与小硬盘速度的问题。它看起来像SQLite的删除-journal文件,我的应用程序的一个线程走出锁(因为得到运用和删除文件-journal)和其他线程我想要做的不便与DB,但仍的SQLite数据库更新文件...当然,我能做的每一DB调用后一分钟的延迟,但是这不是一个解决方案,因为我需要更多的速度。
There is a multithreads application, that works with large DB file (>600 Mb). "Database is locked" problem started when I added blob data, and started operate with >30 Kb of BLOB data per request. I think problem related to small HDD speed. It looks like SQLite deletes -journal file, one thread of my application got out of lock (because -journal file was applied and deleted), and other my thread want to do smth with DB, but SQLite still updates DB file... Sure, I can do a minute delays after each DB call, but this is not a solution, because I need more speed.
现在我用的每个对话会议(每线程)的实现。所以每个应用对象和许多的ISession对象的一种ISessionFactory。
Now I use session per conversation (per thread) implementation. So there is one ISessionFactory per application object and many ISession objects.
有我的助手类(你可以看到我用IsolationLevel.Serializable和CurrentSessionContext = ThreadStaticSessionContext):
There are my helper classes (as you can see I use IsolationLevel.Serializable and CurrentSessionContext = ThreadStaticSessionContext):
public abstract class nHibernateHelper
{
private static FluentConfiguration _configuration;
private static IPersistenceContext _persistenceContext;
static nHibernateHelper() {}
private static FluentConfiguration ConfigurePersistenceLayer()
{
return Fluently.Configure().Database(FluentNHibernate.Cfg.Db.SQLiteConfiguration.Standard.ShowSql().UsingFile(_fileName).IsolationLevel(IsolationLevel.Serializable).MaxFetchDepth(2)).
Mappings(m => m.FluentMappings.AddFromAssemblyOf<Foo>()).CurrentSessionContext(typeof(ThreadStaticSessionContext).FullName);
}
public static ISession CurrentSession
{
get { return _persistenceContext.CurrentSession; }
}
public static IDisposable OpenConnection()
{
return new DbSession(_persistenceContext);
}
}
public class PersistenceContext : IPersistenceContext, IDisposable
{
private readonly FluentConfiguration _configuration;
private readonly ISessionFactory _sessionFactory;
public PersistenceContext(FluentConfiguration configuration)
{
_configuration = configuration;
_sessionFactory = _configuration.BuildSessionFactory();
}
public FluentConfiguration Configuration { get { return _configuration; } }
public ISessionFactory SessionFactory { get { return _sessionFactory; } }
public ISession CurrentSession
{
get
{
if (!CurrentSessionContext.HasBind(SessionFactory))
{
OnContextualSessionIsNotFound();
}
var contextualSession = SessionFactory.GetCurrentSession();
if (contextualSession == null)
{
OnContextualSessionIsNotFound();
}
return contextualSession;
}
}
public void Dispose()
{
SessionFactory.Dispose();
}
private static void OnContextualSessionIsNotFound()
{
throw new InvalidOperationException("Ambient instance of contextual session is not found. Open the db session before.");
}
}
public class DbSession : IDisposable
{
private readonly ISessionFactory _sessionFactory;
public DbSession(IPersistenceContext persistentContext)
{
_sessionFactory = persistentContext.SessionFactory;
CurrentSessionContext.Bind(_sessionFactory.OpenSession());
}
public void Dispose()
{
var session = CurrentSessionContext.Unbind(_sessionFactory);
if (session != null && session.IsOpen)
{
try
{
if (session.Transaction != null && session.Transaction.IsActive)
{
session.Transaction.Rollback();
}
}
finally
{
session.Dispose();
}
}
}
}
和有资料库辅助类。正如你可以看到有由每一个DB调用锁,因此不能出现并发DB调用,对于不同的线程也因为_locker对象是静态的。
And there is repository helper class. As you can see there are locks by every DB call, so concurrence DB call can't appear, for different threads too, because _locker object is static.
public abstract class BaseEntityRepository<T, TId> : IBaseEntityRepository<T, TId> where T : BaseEntity<TId>
{
private ITransaction _transaction;
protected static readonly object _locker = new object();
public bool Save(T item)
{
bool result = false;
if ((item != null) && (item.IsTransient()))
{
lock (_locker)
{
try
{
_transaction = session.BeginTransaction();
nHibernateHelper.CurrentSession.Save(item);
nHibernateHelper.Flush();
_transaction.Commit();
result = true;
} catch
{
_transaction.Rollback();
throw;
}
//DelayAfterProcess();
}
}
return result;
}
//same for delete and update
public T Get(TId itemId)
{
T result = default(T);
lock (_locker)
{
try
{
result = nHibernateHelper.CurrentSession.Get<T>(itemId);
}
catch
{
throw;
}
}
return result;
}
public IList<T> Find(Expression<Func<T, bool>> predicate)
{
IList<T> result = new List<T>();
lock (_locker)
{
try
{
result = nHibernateHelper.CurrentSession.Query<T>().Where(predicate).ToList();
}
catch
{
throw;
}
}
return result;
}
}
我用以前的这样的课(我称之为nHibernateHelper.OpenConnection()每个线程一次)。库是由singletone实例:
I use previous classes like this (I call nHibernateHelper.OpenConnection() once per thread). Repository is instantiated by singletone:
using (nHibernateHelper.OpenConnection())
{
Foo foo = new Foo();
FooRepository.Instance.Save(foo);
}
我试图的IsolationLevel改变ReadCommited,但这不改变的问题。此外,我试图从日记解决通过改变SQLite的日记模式这个问题WAL:
I tried to change IsolationLevel to ReadCommited, but this not changes a problem. Also I tried to solve this problem by change SQLite journal mode from journal to WAL:
using (nHibernateHelper.OpenConnection())
{
using (IDbCommand command = nHibernateHelper.CurrentSession.Connection.CreateCommand())
{
command.CommandText = "PRAGMA journal_mode=WAL";
command.ExecuteNonQuery();
}
}
这有助于快速硬盘的计算机上,但在一些我得到了同样的错误。然后我尝试添加DB更新文件中存在检查库,每个延迟之后保存/更新/删除程序:
This helped on computers with fast HDD, but on some I got same error. Then I tried to add "DB update file exist" check to repository, and delay after each save/update/delete procedure:
protected static int _delayAfterInSeconds = 1;
protected void DelayAfterProcess()
{
bool dbUpdateInProcess = false;
do
{
string fileMask = "*-wal*";
string[] files = Directory.GetFiles(Directory.GetCurrentDirectory(), fileMask);
if ((files != null) && (files.Length > 0))
{
dbUpdateInProcess = true;
Thread.Sleep(1000);
}
else
{
dbUpdateInProcess = false;
}
} while (dbUpdateInProcess);
if (_delayAfterInSeconds > 0)
{
Thread.Sleep(_delayAfterInSeconds * 1000);
}
}
同样的解决方案(检查DB更新文件)没有奏效对于-journal文件。据报道,这-journal文件被删除了,但我仍然有错误。对于它的工作原理-wal文件(因为我觉得我需要更多的时间来测试它)。但这种解决方案认真制动程序。
Same solution (check for DB update file) not worked for -journal file. It reported, that -journal file was deleted, but I still got errors. For -wal file it works (as I think. I need more time to test it). But this solution seriously brake program.
也许你能帮助我吗?
推荐答案
回答自己。
问题涉及.IsolationLevel(IsolationLevel将序列化)。当我改变了这一行.IsolationLevel(IsolationLevel将 READCOMMITTED )的问题消失了。
Answering to myself. Problem was related to .IsolationLevel(IsolationLevel.Serializable). When I changed this line to .IsolationLevel(IsolationLevel.ReadCommitted) problem disappeared.
这篇关于SQLite的"数据库被锁定"错误多线程应用程序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!