SHARE
TWEET

SQLiteTransaction rollback bug

a guest Oct 27th, 2016 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. [TestMethod]
  2. public void TestTransactions() {
  3.     SQLiteConnection connection = ...;
  4.  
  5.     // Create a test table.
  6.     using (SQLiteCommand command = new SQLiteCommand("CREATE TABLE test(column TEXT);", connection)) {
  7.         command.ExecuteNonQuery();
  8.     }
  9.  
  10.     // Insert a single row.
  11.     using (SQLiteCommand command = new SQLiteCommand("INSERT INTO test VALUES ('no transaction');", connection)) {
  12.         command.ExecuteNonQuery();
  13.     }
  14.  
  15.     // Assert that the table contains a single row. Test PASSES.
  16.     using (SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM test;", connection)) {
  17.         Assert.AreEqual((long)1, command.ExecuteScalar());
  18.     }
  19.  
  20.     // Begin a transaction.
  21.     using (SQLiteTransaction transaction = connection.BeginTransaction()) {
  22.                
  23.         // Insert another row into the table.
  24.         using (SQLiteCommand command = new SQLiteCommand("INSERT INTO test VALUES ('in transaction');", connection)) {
  25.             command.ExecuteNonQuery();
  26.         }
  27.  
  28.         // Assert that the table contains two rows. Test PASSES.
  29.         using (SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM test;", connection)) {
  30.             Assert.AreEqual((long)2, command.ExecuteScalar());
  31.         }
  32.  
  33.         // Begin another transaction. System.Data.SQLite apparently allows me to do this, as it doesn't throw any exception.
  34.         // Also, the implementation of SQLiteTransaction suggests that transactions can actually be nested.
  35.         using (SQLiteTransaction nestedTransaction = connection.BeginTransaction()) {
  36.  
  37.             // Insert another row into the table.
  38.             using (SQLiteCommand command = new SQLiteCommand("INSERT INTO test VALUES ('in nested transaction');", connection)) {
  39.                 command.ExecuteNonQuery();
  40.             }
  41.  
  42.             // Assert that the table contains three rows. Test PASSES.
  43.             using (SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM test;", connection)) {
  44.                 Assert.AreEqual((long)3, command.ExecuteScalar());
  45.             }
  46.  
  47.             // Note: the nested transaction goes out of scope here without a call to Commit(), so an implicit Rollback() is done.
  48.         }
  49.  
  50.         // The nested transaction has been rolled back again. Assert that the table now contains two rows again. Test FAILS,
  51.         // since actual row count == 1.
  52.         using (SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM test;", connection)) {
  53.             Assert.AreEqual((long)2, command.ExecuteScalar());
  54.         }
  55.  
  56.         // Note: transaction goes out of scope here without a call to Commit(), so an implicit Rollback() is done.
  57.     }
  58.  
  59.     // All transactions have been rolled back again. Assert that the table now contains a single row again. Test PASSES.
  60.     using (SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM test;", connection)) {
  61.         Assert.AreEqual((long)1, command.ExecuteScalar());
  62.     }
  63. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top