Index: System.Data.SQLite/SQLiteBase.cs ================================================================== --- System.Data.SQLite/SQLiteBase.cs +++ System.Data.SQLite/SQLiteBase.cs @@ -1039,10 +1039,25 @@ /// should return non-zero if there were ever any rows in the associated /// result sets. /// StickyHasRows = 0x400000, + /// + /// Enable "strict" transaction enlistment semantics. Setting this flag + /// will cause an exception to be thrown if an attempt is made to enlist + /// in a transaction with an unavailable or unsupported isolation level. + /// In the future, more extensive checks may be enabled by this flag as + /// well. + /// + StrictEnlistment = 0x800000, + + /// + /// Enable mapping of unsupported transaction isolation levels to the + /// closest supported transaction isolation level. + /// + MapIsolationLevels = 0x1000000, + /// /// When binding parameter values or returning column values, always /// treat them as though they were plain text (i.e. no numeric, /// date/time, or other conversions should be attempted). /// Index: System.Data.SQLite/SQLiteConnection.cs ================================================================== --- System.Data.SQLite/SQLiteConnection.cs +++ System.Data.SQLite/SQLiteConnection.cs @@ -336,10 +336,13 @@ /// internal const string DefaultBaseSchemaName = "sqlite_default_schema"; private const string MemoryFileName = ":memory:"; + internal const IsolationLevel DeferredIsolationLevel = IsolationLevel.ReadCommitted; + internal const IsolationLevel ImmediateIsolationLevel = IsolationLevel.Serializable; + private const SQLiteConnectionFlags DefaultFlags = SQLiteConnectionFlags.Default; private const SQLiteSynchronousEnum DefaultSynchronous = SQLiteSynchronousEnum.Default; private const SQLiteJournalModeEnum DefaultJournalMode = SQLiteJournalModeEnum.Default; private const IsolationLevel DefaultIsolationLevel = IsolationLevel.Serializable; private const SQLiteDateFormats DefaultDateTimeFormat = SQLiteDateFormats.ISO8601; @@ -1239,11 +1242,11 @@ /// /// /// The fallback default isolation level for this connection instance -OR- /// if it cannot be determined. /// - internal static IsolationLevel GetFallbackDefaultIsolationLevel() + private static IsolationLevel GetFallbackDefaultIsolationLevel() { return DefaultIsolationLevel; } /// @@ -1269,11 +1272,11 @@ /// Returns a SQLiteTransaction object. [Obsolete("Use one of the standard BeginTransaction methods, this one will be removed soon")] public SQLiteTransaction BeginTransaction(IsolationLevel isolationLevel, bool deferredLock) { CheckDisposed(); - return (SQLiteTransaction)BeginDbTransaction(deferredLock == false ? IsolationLevel.Serializable : IsolationLevel.ReadCommitted); + return (SQLiteTransaction)BeginDbTransaction(deferredLock == false ? ImmediateIsolationLevel : DeferredIsolationLevel); } /// /// OBSOLETE. Creates a new SQLiteTransaction if one isn't already active on the connection. /// @@ -1283,11 +1286,11 @@ /// Returns a SQLiteTransaction object. [Obsolete("Use one of the standard BeginTransaction methods, this one will be removed soon")] public SQLiteTransaction BeginTransaction(bool deferredLock) { CheckDisposed(); - return (SQLiteTransaction)BeginDbTransaction(deferredLock == false ? IsolationLevel.Serializable : IsolationLevel.ReadCommitted); + return (SQLiteTransaction)BeginDbTransaction(deferredLock == false ? ImmediateIsolationLevel : DeferredIsolationLevel); } /// /// Creates a new if one isn't already active on the connection. /// @@ -1328,16 +1331,17 @@ { if (_connectionState != ConnectionState.Open) throw new InvalidOperationException(); if (isolationLevel == IsolationLevel.Unspecified) isolationLevel = _defaultIsolation; + isolationLevel = GetEffectiveIsolationLevel(isolationLevel); - if (isolationLevel != IsolationLevel.Serializable && isolationLevel != IsolationLevel.ReadCommitted) + if (isolationLevel != ImmediateIsolationLevel && isolationLevel != DeferredIsolationLevel) throw new ArgumentException("isolationLevel"); SQLiteTransaction transaction = - new SQLiteTransaction(this, isolationLevel != IsolationLevel.Serializable); + new SQLiteTransaction(this, isolationLevel != ImmediateIsolationLevel); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.NewTransaction, null, transaction, null, null, null, null, null)); @@ -1861,11 +1865,16 @@ if (_transactionLevel > 0 && transaction != null) throw new ArgumentException("Unable to enlist in transaction, a local transaction already exists"); else if (transaction == null) throw new ArgumentNullException("Unable to enlist in transaction, it is null"); - _enlistment = new SQLiteEnlistment(this, transaction); + bool strictEnlistment = ((_flags & SQLiteConnectionFlags.StrictEnlistment) == + SQLiteConnectionFlags.StrictEnlistment); + + _enlistment = new SQLiteEnlistment(this, transaction, + GetFallbackDefaultIsolationLevel(), strictEnlistment, + strictEnlistment); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.EnlistTransaction, null, null, null, null, null, null, new object[] { _enlistment })); } @@ -2180,10 +2189,49 @@ result = false; } return result; } + + /// + /// Determines the transaction isolation level that should be used by + /// the caller, primarily based upon the one specified by the caller. + /// If mapping of transaction isolation levels is enabled, the returned + /// transaction isolation level may be significantly different than the + /// originally specified one. + /// + /// + /// The originally specified transaction isolation level. + /// + /// + /// The transaction isolation level that should be used. + /// + private IsolationLevel GetEffectiveIsolationLevel( + IsolationLevel isolationLevel + ) + { + if ((_flags & SQLiteConnectionFlags.MapIsolationLevels) + != SQLiteConnectionFlags.MapIsolationLevels) + { + return isolationLevel; + } + + switch (isolationLevel) + { + case IsolationLevel.Unspecified: + case IsolationLevel.Chaos: + case IsolationLevel.ReadUncommitted: + case IsolationLevel.ReadCommitted: + return DeferredIsolationLevel; + case IsolationLevel.RepeatableRead: + case IsolationLevel.Serializable: + case IsolationLevel.Snapshot: + return ImmediateIsolationLevel; + default: + return GetFallbackDefaultIsolationLevel(); + } + } /// /// Opens the connection using the parameters found in the . /// public override void Open() @@ -2282,12 +2330,13 @@ _defaultTimeout = Convert.ToInt32(FindKey(opts, "Default Timeout", DefaultConnectionTimeout.ToString()), CultureInfo.InvariantCulture); enumValue = TryParseEnum(typeof(IsolationLevel), FindKey(opts, "Default IsolationLevel", DefaultIsolationLevel.ToString()), true); _defaultIsolation = (enumValue is IsolationLevel) ? (IsolationLevel)enumValue : DefaultIsolationLevel; + _defaultIsolation = GetEffectiveIsolationLevel(_defaultIsolation); - if (_defaultIsolation != IsolationLevel.Serializable && _defaultIsolation != IsolationLevel.ReadCommitted) + if (_defaultIsolation != ImmediateIsolationLevel && _defaultIsolation != DeferredIsolationLevel) throw new NotSupportedException("Invalid Default IsolationLevel specified"); _baseSchemaName = FindKey(opts, "BaseSchemaName", DefaultBaseSchemaName); if (_sql == null) Index: System.Data.SQLite/SQLiteEnlistment.cs ================================================================== --- System.Data.SQLite/SQLiteEnlistment.cs +++ System.Data.SQLite/SQLiteEnlistment.cs @@ -6,22 +6,30 @@ ********************************************************/ #if !PLATFORM_COMPACTFRAMEWORK namespace System.Data.SQLite { - using System.Transactions; + using System.Globalization; + using System.Transactions; internal sealed class SQLiteEnlistment : IDisposable, IEnlistmentNotification { internal SQLiteTransaction _transaction; internal Transaction _scope; internal bool _disposeConnection; - internal SQLiteEnlistment(SQLiteConnection cnn, Transaction scope) + internal SQLiteEnlistment( + SQLiteConnection cnn, + Transaction scope, + System.Data.IsolationLevel defaultIsolationLevel, + bool throwOnUnavailable, + bool throwOnUnsupported + ) { _transaction = cnn.BeginTransaction(GetSystemDataIsolationLevel( - cnn, scope)); + cnn, scope, defaultIsolationLevel, throwOnUnavailable, + throwOnUnsupported)); _scope = scope; _scope.EnlistVolatile(this, System.Transactions.EnlistmentOptions.None); } @@ -29,22 +37,33 @@ /////////////////////////////////////////////////////////////////////////// #region Private Methods private System.Data.IsolationLevel GetSystemDataIsolationLevel( SQLiteConnection connection, - Transaction transaction + Transaction transaction, + System.Data.IsolationLevel defaultIsolationLevel, + bool throwOnUnavailable, + bool throwOnUnsupported ) { if (transaction == null) { // - // TODO: Perhaps throw an exception here if the connection - // is null? + // NOTE: If neither the transaction nor connection isolation + // level is available, throw an exception if instructed + // by the caller. // - return (connection != null) ? - connection.GetDefaultIsolationLevel() : - SQLiteConnection.GetFallbackDefaultIsolationLevel(); + if (connection != null) + return connection.GetDefaultIsolationLevel(); + + if (throwOnUnavailable) + { + throw new InvalidOperationException( + "isolation level is unavailable"); + } + + return defaultIsolationLevel; } System.Transactions.IsolationLevel isolationLevel = transaction.IsolationLevel; @@ -51,30 +70,39 @@ // // TODO: Are these isolation level mappings actually correct? // switch (isolationLevel) { + case IsolationLevel.Unspecified: + return System.Data.IsolationLevel.Unspecified; case IsolationLevel.Chaos: return System.Data.IsolationLevel.Chaos; - case IsolationLevel.ReadCommitted: - return System.Data.IsolationLevel.ReadCommitted; case IsolationLevel.ReadUncommitted: return System.Data.IsolationLevel.ReadUncommitted; + case IsolationLevel.ReadCommitted: + return System.Data.IsolationLevel.ReadCommitted; case IsolationLevel.RepeatableRead: return System.Data.IsolationLevel.RepeatableRead; case IsolationLevel.Serializable: return System.Data.IsolationLevel.Serializable; case IsolationLevel.Snapshot: return System.Data.IsolationLevel.Snapshot; - case IsolationLevel.Unspecified: - return System.Data.IsolationLevel.Unspecified; + } + + // + // NOTE: When in "strict" mode, throw an exception if the isolation + // level is not recognized; otherwise, fallback to the default + // isolation level specified by the caller. + // + if (throwOnUnsupported) + { + throw new InvalidOperationException( + String.Format(CultureInfo.InvariantCulture, + "unsupported isolation level {0}", isolationLevel)); } - // - // TODO: Perhaps throw an exception here? - // - return SQLiteConnection.GetFallbackDefaultIsolationLevel(); + return defaultIsolationLevel; } /////////////////////////////////////////////////////////////////////////// private void Cleanup(SQLiteConnection cnn) Index: System.Data.SQLite/SQLiteTransaction.cs ================================================================== --- System.Data.SQLite/SQLiteTransaction.cs +++ System.Data.SQLite/SQLiteTransaction.cs @@ -32,11 +32,13 @@ internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock) { _cnn = connection; _version = _cnn._version; - _level = (deferredLock == true) ? IsolationLevel.ReadCommitted : IsolationLevel.Serializable; + _level = (deferredLock == true) ? + SQLiteConnection.DeferredIsolationLevel : + SQLiteConnection.ImmediateIsolationLevel; if (_cnn._transactionLevel++ == 0) { try { Index: Tests/tkt-56b42d99c1.eagle ================================================================== --- Tests/tkt-56b42d99c1.eagle +++ Tests/tkt-56b42d99c1.eagle @@ -408,10 +408,411 @@ unset -nocomplain result results errors code sql dataSource id db fileName } -constraints {eagle monoBug28 command.sql compile.DATA SQLite\ System.Data.SQLite compileCSharp} -match regexp -result {^Ok\ System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 1$}} + +############################################################################### + +set flags MapIsolationLevels + +############################################################################### + +runTest {test tkt-56b42d99c1-1.6 {enlisted transaction isolation} -setup { + setupDb [set fileName tkt-56b42d99c1-1.6.db] +} -body { + set id [object invoke Interpreter.GetActive NextId] + set dataSource [file join [getDatabaseDirectory] $fileName] + + unset -nocomplain results errors + + set code [compileCSharpWith [subst { + using System.Data.SQLite; + using System.Reflection; + using System.Transactions; + + namespace _Dynamic${id} + { + public static class Test${id} + { + public static bool TryEnlistInTransaction() + { + TransactionOptions transactionOptions = new TransactionOptions(); + transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted; + + using (TransactionScope transactionScope = new TransactionScope( + TransactionScopeOption.Required, transactionOptions)) + { + using (SQLiteConnection connection1 = new SQLiteConnection( + "Data Source=${dataSource};[getFlagsProperty $flags]")) + { + connection1.Open(); + + using (SQLiteConnection connection2 = new SQLiteConnection( + "Data Source=${dataSource};[getFlagsProperty $flags]")) + { + connection2.Open(); + + BindingFlags bindingFlags = BindingFlags.Instance | + BindingFlags.NonPublic | BindingFlags.GetField; + + FieldInfo fieldInfo1 = connection1.GetType().GetField( + "_enlistment", bindingFlags); + + object enlistment1 = fieldInfo1.GetValue(connection1); + object enlistment2 = fieldInfo1.GetValue(connection2); + + FieldInfo fieldInfo2 = enlistment1.GetType().GetField( + "_transaction", bindingFlags); + + SQLiteTransaction transaction1 = + (SQLiteTransaction)fieldInfo2.GetValue(enlistment1); + + SQLiteTransaction transaction2 = + (SQLiteTransaction)fieldInfo2.GetValue(enlistment2); + + return (transaction1.IsolationLevel == + transaction2.IsolationLevel); + } + } + } + } + + /////////////////////////////////////////////////////////////////////// + + public static void Main() + { + // do nothing. + } + } + } + }] true true true results errors System.Data.SQLite.dll] + + list $code $results \ + [expr {[info exists errors] ? $errors : ""}] \ + [expr {$code eq "Ok" ? [catch { + object invoke _Dynamic${id}.Test${id} TryEnlistInTransaction + } result] : [set result ""]}] $result +} -cleanup { + cleanupDb $fileName + + unset -nocomplain result results errors code dataSource id db fileName +} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\ +System.Data.SQLite compileCSharp} -match regexp -result {^Ok\ +System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 True$}} + +############################################################################### + +runTest {test tkt-56b42d99c1-1.7 {enlisted transaction isolation} -setup { + setupDb [set fileName tkt-56b42d99c1-1.7.db] +} -body { + set id [object invoke Interpreter.GetActive NextId] + set dataSource [file join [getDatabaseDirectory] $fileName] + + unset -nocomplain results errors + + set sql(1) { \ + CREATE TABLE t1(x); \ + INSERT INTO t1 (x) VALUES(1); \ + } + + set sql(2) { \ + SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'; \ + } + + set code [compileCSharpWith [subst { + using System.Data.SQLite; + using System.Transactions; + + namespace _Dynamic${id} + { + public static class Test${id} + { + public static int Main() + { + TransactionOptions transactionOptions = new TransactionOptions(); + transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted; + + using (TransactionScope transactionScope = new TransactionScope( + TransactionScopeOption.Required, transactionOptions)) + { + using (SQLiteConnection connection1 = new SQLiteConnection( + "Data Source=${dataSource};[getFlagsProperty $flags]")) + { + connection1.Open(); + + using (SQLiteConnection connection2 = new SQLiteConnection( + "Data Source=${dataSource};[getFlagsProperty $flags]")) + { + connection2.Open(); + + using (SQLiteCommand command1 = connection1.CreateCommand()) + { + command1.CommandText = "${sql(1)}"; + command1.ExecuteNonQuery(); + + using (SQLiteCommand command2 = connection2.CreateCommand()) + { + command2.CommandText = "${sql(2)}"; + return (int)(long)command2.ExecuteScalar(); + } + } + } + } + } + } + } + } + }] true true true results errors System.Data.SQLite.dll] + + list $code $results \ + [expr {[info exists errors] ? $errors : ""}] \ + [expr {$code eq "Ok" ? [catch { + object invoke _Dynamic${id}.Test${id} Main + } result] : [set result ""]}] $result +} -cleanup { + cleanupDb $fileName + + unset -nocomplain result results errors code sql dataSource id db fileName +} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\ +System.Data.SQLite compileCSharp} -match regexp -result {^Ok\ +System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 0$}} + +############################################################################### + +runTest {test tkt-56b42d99c1-1.8 {enlisted transaction isolation} -setup { + setupDb [set fileName tkt-56b42d99c1-1.8.db] +} -body { + set id [object invoke Interpreter.GetActive NextId] + set dataSource [file join [getDatabaseDirectory] $fileName] + + unset -nocomplain results errors + + set sql(1) { \ + CREATE TABLE t1(x); \ + INSERT INTO t1 (x) VALUES(1); \ + } + + set sql(2) { \ + SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'; \ + } + + set code [compileCSharpWith [subst { + using System.Data.SQLite; + using System.Transactions; + + namespace _Dynamic${id} + { + public static class Test${id} + { + public static int Main() + { + TransactionOptions transactionOptions = new TransactionOptions(); + transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted; + + using (TransactionScope transactionScope = new TransactionScope( + TransactionScopeOption.Required, transactionOptions)) + { + using (SQLiteConnection connection1 = new SQLiteConnection( + "Data Source=${dataSource};Enlist=False;[getFlagsProperty $flags]")) + { + connection1.Open(); + + using (SQLiteConnection connection2 = new SQLiteConnection( + "Data Source=${dataSource};Enlist=False;[getFlagsProperty $flags]")) + { + connection2.Open(); + + using (SQLiteCommand command1 = connection1.CreateCommand()) + { + command1.CommandText = "${sql(1)}"; + command1.ExecuteNonQuery(); + + using (SQLiteCommand command2 = connection2.CreateCommand()) + { + command2.CommandText = "${sql(2)}"; + return (int)(long)command2.ExecuteScalar(); + } + } + } + } + } + } + } + } + }] true true true results errors System.Data.SQLite.dll] + + list $code $results \ + [expr {[info exists errors] ? $errors : ""}] \ + [expr {$code eq "Ok" ? [catch { + object invoke _Dynamic${id}.Test${id} Main + } result] : [set result ""]}] $result +} -cleanup { + cleanupDb $fileName + + unset -nocomplain result results errors code sql dataSource id db fileName +} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\ +System.Data.SQLite compileCSharp} -match regexp -result {^Ok\ +System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 1$}} + +############################################################################### + +runTest {test tkt-56b42d99c1-1.9 {enlisted transaction isolation} -setup { + setupDb [set fileName tkt-56b42d99c1-1.9.db] +} -body { + set id [object invoke Interpreter.GetActive NextId] + set dataSource [file join [getDatabaseDirectory] $fileName] + + unset -nocomplain results errors + + set sql(1) { \ + CREATE TABLE t1(x); \ + INSERT INTO t1 (x) VALUES(1); \ + } + + set sql(2) { \ + SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'; \ + } + + set code [compileCSharpWith [subst { + using System.Data.SQLite; + using System.Transactions; + + namespace _Dynamic${id} + { + public static class Test${id} + { + public static int Main() + { + TransactionOptions transactionOptions = new TransactionOptions(); + transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted; + + using (TransactionScope transactionScope = new TransactionScope( + TransactionScopeOption.Required, transactionOptions)) + { + using (SQLiteConnection connection1 = new SQLiteConnection( + "Data Source=${dataSource};[getFlagsProperty $flags]")) + { + connection1.Open(); + + using (SQLiteConnection connection2 = new SQLiteConnection( + "Data Source=${dataSource};Enlist=False;[getFlagsProperty $flags]")) + { + connection2.Open(); + + using (SQLiteCommand command1 = connection1.CreateCommand()) + { + command1.CommandText = "${sql(1)}"; + command1.ExecuteNonQuery(); + + using (SQLiteCommand command2 = connection2.CreateCommand()) + { + command2.CommandText = "${sql(2)}"; + return (int)(long)command2.ExecuteScalar(); + } + } + } + } + } + } + } + } + }] true true true results errors System.Data.SQLite.dll] + + list $code $results \ + [expr {[info exists errors] ? $errors : ""}] \ + [expr {$code eq "Ok" ? [catch { + object invoke _Dynamic${id}.Test${id} Main + } result] : [set result ""]}] $result +} -cleanup { + cleanupDb $fileName + + unset -nocomplain result results errors code sql dataSource id db fileName +} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\ +System.Data.SQLite compileCSharp} -match regexp -result {^Ok\ +System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 0$}} + +############################################################################### + +runTest {test tkt-56b42d99c1-1.10 {enlisted transaction isolation} -setup { + setupDb [set fileName tkt-56b42d99c1-1.10.db] +} -body { + set id [object invoke Interpreter.GetActive NextId] + set dataSource [file join [getDatabaseDirectory] $fileName] + + unset -nocomplain results errors + + set sql(1) { \ + CREATE TABLE t1(x); \ + INSERT INTO t1 (x) VALUES(1); \ + } + + set sql(2) { \ + SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'; \ + } + + set code [compileCSharpWith [subst { + using System.Data.SQLite; + using System.Transactions; + + namespace _Dynamic${id} + { + public static class Test${id} + { + public static int Main() + { + TransactionOptions transactionOptions = new TransactionOptions(); + transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted; + + using (TransactionScope transactionScope = new TransactionScope( + TransactionScopeOption.Required, transactionOptions)) + { + using (SQLiteConnection connection1 = new SQLiteConnection( + "Data Source=${dataSource};Enlist=False;[getFlagsProperty $flags]")) + { + connection1.Open(); + + using (SQLiteConnection connection2 = new SQLiteConnection( + "Data Source=${dataSource};[getFlagsProperty $flags]")) + { + connection2.Open(); + + using (SQLiteCommand command1 = connection1.CreateCommand()) + { + command1.CommandText = "${sql(1)}"; + command1.ExecuteNonQuery(); + + using (SQLiteCommand command2 = connection2.CreateCommand()) + { + command2.CommandText = "${sql(2)}"; + return (int)(long)command2.ExecuteScalar(); + } + } + } + } + } + } + } + } + }] true true true results errors System.Data.SQLite.dll] + + list $code $results \ + [expr {[info exists errors] ? $errors : ""}] \ + [expr {$code eq "Ok" ? [catch { + object invoke _Dynamic${id}.Test${id} Main + } result] : [set result ""]}] $result +} -cleanup { + cleanupDb $fileName + + unset -nocomplain result results errors code sql dataSource id db fileName +} -constraints {eagle monoBug28 command.sql compile.DATA SQLite\ +System.Data.SQLite compileCSharp} -match regexp -result {^Ok\ +System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 1$}} + +############################################################################### + +unset -nocomplain flags ############################################################################### runSQLiteTestEpilogue runTestEpilogue