System.Data.SQLite 不支持多事务 [英] System.Data.SQLite Not Supporting Multiple Transactions
问题描述
所以我在 System.Data.SQLite 和使用多个事务时遇到了一个有趣的问题.基本上我有以下代码失败:
So I am having an interesting issue with System.Data.SQLite and using multiple transactions. Basically I have the following code which fails:
using (IDbConnection connection1 = new SQLiteConnection("connectionstring"), connection2 = new SQLiteConnection("connectionstring"))
{
connection1.Open();
connection2.Open();
IDbTransaction transaction1 = connection1.BeginTransaction();
IDbTransaction transaction2 = connection2.BeginTransaction(); // Fails!
using(IDbCommand command = new SQLiteCommand())
{
command.Text = "CREATE TABLE artist(artistid int, artistname text);";
command.CommandType = CommandType.Text;
command.Connection = connection1;
command.ExecuteNonQuery();
}
using (IDbCommand command = new SQLiteCommand())
{
command.Text = "CREATE TABLE track(trackid int, trackname text);";
command.CommandType = CommandType.Text;
command.Connection = connection2;
command.ExecuteNonQuery();
}
transaction1.Commit();
transaction2.Commit();
}
从我读到的内容来看,System.Data.SQLite 似乎应该支持嵌套和扩展顺序事务.代码在第 7 行(声明第二个事务的地方)失败,但出现以下异常:
From what I've read it seems that System.Data.SQLite should support nested and by extension sequential transactions. The code fails on line 7 (where the second transaction is declared) with the following exception:
System.Data.SQLite.SQLiteException: The database file is locked
System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
System.Data.SQLite.SQLiteDataReader.NextResult()
System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
System.Data.SQLite.SQLiteTransaction..ctor(SQLiteConnection connection, Boolean deferredLock)
System.Data.SQLite.SQLiteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction()
有谁知道问题是什么或如何解决这个问题?我觉得并发事务对于任何数据库系统都是必不可少的,因此必须有某种方法来做到这一点.
Does anyone know what the issue is or how to get around this? I feel having concurrent transactions is essential for any database system so there must be some way to do this.
谢谢!
推荐答案
OP 在 2 个连接上启动事务,这是问题开始的地方,而不是多个事务本身.
OP is initiating transactions on 2 connections, that's where problems start, not multiple transactions per se.
SQLiteConnection conn = new SQLiteConnection("data source=:memory:");
conn.Open();
var command = conn.CreateCommand();
command.CommandText = "create table a (b integer primary key autoincrement, c text)";
command.ExecuteNonQuery();
var tran1 = conn.BeginTransaction();
var tran2 = conn.BeginTransaction();
var command1 = conn.CreateCommand();
var command2 = conn.CreateCommand();
command1.Transaction = tran1;
command2.Transaction = tran2;
command1.CommandText = "insert into a VALUES (NULL, 'bla1')";
command2.CommandText = "insert into a VALUES (NULL, 'bla2')";
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
tran1.Commit();
tran2.Commit();
command.CommandText = "select count(*) from a";
Console.WriteLine(command.ExecuteScalar());
这篇关于System.Data.SQLite 不支持多事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!