System.Data.SQLite
Check-in [712e256f4f]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Use savepoints to implement nested transaction support.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-1f7bfff467
Files: files | file ages | folders
SHA1: 712e256f4f0433abc15cbbda5495c30fa6ec3686
User & Date: mistachkin 2016-10-28 20:09:33
References
2016-10-28
20:10 Ticket [1f7bfff467] SQLiteTransaction: critical rollback bug status still Open with 3 other changes artifact: cdbe5dda74 user: mistachkin
Context
2016-10-28
21:08
Update version history docs. check-in: 15904edeb7 user: mistachkin tags: tkt-1f7bfff467
20:09
Use savepoints to implement nested transaction support. check-in: 712e256f4f user: mistachkin tags: tkt-1f7bfff467
2016-10-27
20:42
Draft changes to address ticket [1f7bfff467]. check-in: 0b11868e92 user: mistachkin tags: tkt-1f7bfff467
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to System.Data.SQLite/SQLiteTransaction.cs.

     3      3    * Written by Robert Simpson (robert@blackcastlesoft.com)
     4      4    *
     5      5    * Released to the public domain, use at your own risk!
     6      6    ********************************************************/
     7      7   
     8      8   namespace System.Data.SQLite
     9      9   {
    10         -  using System;
    11         -  using System.Data;
    12         -  using System.Data.Common;
    13         -  using System.Threading;
    14         -
    15         -  /// <summary>
    16         -  /// SQLite implementation of DbTransaction.
    17         -  /// </summary>
    18         -  public sealed class SQLiteTransaction : DbTransaction
    19         -  {
    20         -    /// <summary>
    21         -    /// The connection to which this transaction is bound
    22         -    /// </summary>
    23         -    internal SQLiteConnection _cnn;
    24         -    internal int _version; // Matches the version of the connection
    25         -    private IsolationLevel _level;
    26         -
    27         -    /// <summary>
    28         -    /// Constructs the transaction object, binding it to the supplied connection
    29         -    /// </summary>
    30         -    /// <param name="connection">The connection to open a transaction on</param>
    31         -    /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
    32         -    internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock)
    33         -    {
    34         -      _cnn = connection;
    35         -      _version = _cnn._version;
    36         -
    37         -      _level = (deferredLock == true) ?
    38         -          SQLiteConnection.DeferredIsolationLevel :
    39         -          SQLiteConnection.ImmediateIsolationLevel;
    40         -
    41         -      if (_cnn._transactionLevel++ == 0)
    42         -      {
    43         -        try
    44         -        {
    45         -          using (SQLiteCommand cmd = _cnn.CreateCommand())
    46         -          {
    47         -            if (!deferredLock)
    48         -              cmd.CommandText = "BEGIN IMMEDIATE";
    49         -            else
    50         -              cmd.CommandText = "BEGIN";
    51         -
    52         -            cmd.ExecuteNonQuery();
    53         -          }
    54         -        }
    55         -        catch (SQLiteException)
    56         -        {
    57         -          _cnn._transactionLevel--;
    58         -          _cnn = null;
    59         -          throw;
    60         -        }
    61         -      }
    62         -    }
    63         -
    64         -    ///////////////////////////////////////////////////////////////////////////////////////////////
    65         -
    66         -    #region IDisposable "Pattern" Members
    67         -    private bool disposed;
    68         -    private void CheckDisposed() /* throw */
    69         -    {
    70         -#if THROW_ON_DISPOSED
    71         -        if (disposed)
    72         -            throw new ObjectDisposedException(typeof(SQLiteTransaction).Name);
    73         -#endif
    74         -    }
           10  +    using System;
           11  +    using System.Data;
           12  +    using System.Data.Common;
           13  +    using System.Threading;
    75     14   
    76     15       ///////////////////////////////////////////////////////////////////////////////////////////////
    77     16   
    78     17       /// <summary>
    79         -    /// Disposes the transaction.  If it is currently active, any changes are rolled back.
           18  +    /// SQLite implementation of DbTransaction.
    80     19       /// </summary>
    81         -    protected override void Dispose(bool disposing)
           20  +    public sealed class SQLiteTransaction : DbTransaction
    82     21       {
    83         -        try
           22  +        /// <summary>
           23  +        /// The connection to which this transaction is bound
           24  +        /// </summary>
           25  +        internal SQLiteConnection _cnn;
           26  +        internal int _version; // Matches the version of the connection
           27  +        private IsolationLevel _level;
           28  +
           29  +        /// <summary>
           30  +        /// Constructs the transaction object, binding it to the supplied connection
           31  +        /// </summary>
           32  +        /// <param name="connection">The connection to open a transaction on</param>
           33  +        /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
           34  +        internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock)
           35  +        {
           36  +            _cnn = connection;
           37  +            _version = _cnn._version;
           38  +
           39  +            _level = (deferredLock == true) ?
           40  +                SQLiteConnection.DeferredIsolationLevel :
           41  +                SQLiteConnection.ImmediateIsolationLevel;
           42  +
           43  +            int level;
           44  +
           45  +            if ((level = _cnn._transactionLevel++) == 0)
           46  +            {
           47  +                try
           48  +                {
           49  +                    using (SQLiteCommand cmd = _cnn.CreateCommand())
           50  +                    {
           51  +                        if (!deferredLock)
           52  +                            cmd.CommandText = "BEGIN IMMEDIATE;";
           53  +                        else
           54  +                            cmd.CommandText = "BEGIN;";
           55  +
           56  +                        cmd.ExecuteNonQuery();
           57  +                    }
           58  +                }
           59  +                catch (SQLiteException)
           60  +                {
           61  +                    _cnn._transactionLevel--;
           62  +                    _cnn = null;
           63  +
           64  +                    throw;
           65  +                }
           66  +            }
           67  +            else
           68  +            {
           69  +                try
           70  +                {
           71  +                    using (SQLiteCommand cmd = _cnn.CreateCommand())
           72  +                    {
           73  +                        cmd.CommandText = String.Format(
           74  +                            "SAVEPOINT {0};", GetSavePointName(level));
           75  +
           76  +                        cmd.ExecuteNonQuery();
           77  +                    }
           78  +                }
           79  +                catch (SQLiteException)
           80  +                {
           81  +                    _cnn._transactionLevel--;
           82  +                    _cnn = null;
           83  +
           84  +                    throw;
           85  +                }
           86  +            }
           87  +        }
           88  +
           89  +        ///////////////////////////////////////////////////////////////////////////////////////////////
           90  +
           91  +        #region IDisposable "Pattern" Members
           92  +        private bool disposed;
           93  +        private void CheckDisposed() /* throw */
           94  +        {
           95  +#if THROW_ON_DISPOSED
           96  +            if (disposed)
           97  +                throw new ObjectDisposedException(typeof(SQLiteTransaction).Name);
           98  +#endif
           99  +        }
          100  +
          101  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          102  +
          103  +        /// <summary>
          104  +        /// Disposes the transaction.  If it is currently active, any changes are rolled back.
          105  +        /// </summary>
          106  +        protected override void Dispose(bool disposing)
          107  +        {
          108  +            try
          109  +            {
          110  +                if (!disposed)
          111  +                {
          112  +                    if (disposing)
          113  +                    {
          114  +                        ////////////////////////////////////
          115  +                        // dispose managed resources here...
          116  +                        ////////////////////////////////////
          117  +
          118  +                        if (IsValid(false))
          119  +                        {
          120  +                            IssueRollback(false);
          121  +                        }
          122  +                    }
          123  +
          124  +                    //////////////////////////////////////
          125  +                    // release unmanaged resources here...
          126  +                    //////////////////////////////////////
          127  +                }
          128  +            }
          129  +            finally
          130  +            {
          131  +                base.Dispose(disposing);
          132  +
          133  +                //
          134  +                // NOTE: Everything should be fully disposed at this point.
          135  +                //
          136  +                disposed = true;
          137  +            }
          138  +        }
          139  +        #endregion
          140  +
          141  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          142  +
          143  +        /// <summary>
          144  +        /// Commits the current transaction.
          145  +        /// </summary>
          146  +        public override void Commit()
          147  +        {
          148  +            CheckDisposed();
          149  +            SQLiteConnection.Check(_cnn);
          150  +            IsValid(true);
          151  +
          152  +            int level = _cnn._transactionLevel;
          153  +
          154  +            if (level - 1 == 0)
          155  +            {
          156  +                using (SQLiteCommand cmd = _cnn.CreateCommand())
          157  +                {
          158  +                    cmd.CommandText = "COMMIT;";
          159  +                    cmd.ExecuteNonQuery();
          160  +                }
          161  +
          162  +                _cnn._transactionLevel--;
          163  +                _cnn = null;
          164  +            }
          165  +            else
          166  +            {
          167  +                using (SQLiteCommand cmd = _cnn.CreateCommand())
          168  +                {
          169  +                    cmd.CommandText = String.Format(
          170  +                        "RELEASE {0};", GetSavePointName(level - 1));
          171  +
          172  +                    cmd.ExecuteNonQuery();
          173  +                }
          174  +
          175  +                _cnn._transactionLevel--;
          176  +            }
          177  +        }
          178  +
          179  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          180  +
          181  +        /// <summary>
          182  +        /// Returns the underlying connection to which this transaction applies.
          183  +        /// </summary>
          184  +        public new SQLiteConnection Connection
          185  +        {
          186  +            get { CheckDisposed(); return _cnn; }
          187  +        }
          188  +
          189  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          190  +
          191  +        /// <summary>
          192  +        /// Forwards to the local Connection property
          193  +        /// </summary>
          194  +        protected override DbConnection DbConnection
          195  +        {
          196  +            get { return Connection; }
          197  +        }
          198  +
          199  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          200  +
          201  +        /// <summary>
          202  +        /// Gets the isolation level of the transaction.  SQLite only supports Serializable transactions.
          203  +        /// </summary>
          204  +        public override IsolationLevel IsolationLevel
          205  +        {
          206  +            get { CheckDisposed(); return _level; }
          207  +        }
          208  +
          209  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          210  +
          211  +        /// <summary>
          212  +        /// Rolls back the active transaction.
          213  +        /// </summary>
          214  +        public override void Rollback()
          215  +        {
          216  +            CheckDisposed();
          217  +            SQLiteConnection.Check(_cnn);
          218  +            IsValid(true);
          219  +            IssueRollback(true);
          220  +        }
          221  +
          222  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          223  +
          224  +        /// <summary>
          225  +        /// Issue a ROLLBACK command against the database connection,
          226  +        /// optionally re-throwing any caught exception.
          227  +        /// </summary>
          228  +        /// <param name="throwError">
          229  +        /// Non-zero to re-throw caught exceptions.
          230  +        /// </param>
          231  +        internal void IssueRollback(bool throwError)
    84    232           {
    85         -            if (!disposed)
          233  +            SQLiteConnection cnn = Interlocked.Exchange(ref _cnn, null);
          234  +
          235  +            if (cnn != null)
    86    236               {
    87         -                if (disposing)
          237  +                int level = cnn._transactionLevel;
          238  +
          239  +                if (level - 1 == 0)
          240  +                {
          241  +                    try
          242  +                    {
          243  +                        using (SQLiteCommand cmd = cnn.CreateCommand())
          244  +                        {
          245  +                            cmd.CommandText = "ROLLBACK;";
          246  +                            cmd.ExecuteNonQuery();
          247  +                        }
          248  +                    }
          249  +                    catch
          250  +                    {
          251  +                        if (throwError)
          252  +                            throw;
          253  +                    }
          254  +                }
          255  +                else
    88    256                   {
    89         -                    ////////////////////////////////////
    90         -                    // dispose managed resources here...
    91         -                    ////////////////////////////////////
          257  +                    try
          258  +                    {
          259  +                        using (SQLiteCommand cmd = cnn.CreateCommand())
          260  +                        {
          261  +                            cmd.CommandText = String.Format(
          262  +                                "ROLLBACK TO {0};", GetSavePointName(level - 1));
    92    263   
    93         -                    if (IsValid(false))
          264  +                            cmd.ExecuteNonQuery();
          265  +                        }
          266  +                    }
          267  +                    catch
    94    268                       {
    95         -                        IssueRollback(false);
          269  +                        if (throwError)
          270  +                            throw;
    96    271                       }
    97    272                   }
    98    273   
    99         -                //////////////////////////////////////
   100         -                // release unmanaged resources here...
   101         -                //////////////////////////////////////
          274  +                cnn._transactionLevel--;
   102    275               }
   103    276           }
          277  +
          278  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          279  +
          280  +        /// <summary>
          281  +        /// Constructs the name of a new or existing savepoint.
          282  +        /// </summary>
          283  +        /// <param name="level">
          284  +        /// The transaction level associated with the connection.
          285  +        /// </param>
          286  +        /// <returns>
          287  +        /// The name of the savepoint -OR- null if it cannot be constructed.
          288  +        /// </returns>
          289  +        private static string GetSavePointName(
          290  +            int level
          291  +            )
          292  +        {
          293  +            return String.Format("sqlite_dotnet_savepoint_{0}", level);
          294  +        }
................................................................................
   104         -        finally
          295  +
          296  +        ///////////////////////////////////////////////////////////////////////////////////////////////
          297  +
          298  +        /// <summary>
          299  +        /// Checks the state of this transaction, optionally throwing an exception if a state inconsistency is found.
          300  +        /// </summary>
          301  +        /// <param name="throwError">
          302  +        /// Non-zero to throw an exception if a state inconsistency is found.
          303  +        /// </param>
          304  +        /// <returns>
          305  +        /// Non-zero if this transaction is valid; otherwise, false.
          306  +        /// </returns>
          307  +        internal bool IsValid(bool throwError)
   105    308           {
   106         -            base.Dispose(disposing);
          309  +            if (_cnn == null)
          310  +            {
          311  +                if (throwError == true) throw new ArgumentNullException("No connection associated with this transaction");
          312  +                else return false;
          313  +            }
          314  +
          315  +            if (_cnn._version != _version)
          316  +            {
          317  +                if (throwError == true) throw new SQLiteException("The connection was closed and re-opened, changes were already rolled back");
          318  +                else return false;
          319  +            }
          320  +            if (_cnn.State != ConnectionState.Open)
          321  +            {
          322  +                if (throwError == true) throw new SQLiteException("Connection was closed");
          323  +                else return false;
          324  +            }
          325  +
          326  +            if (_cnn._transactionLevel == 0 || _cnn._sql.AutoCommit == true)
          327  +            {
          328  +                _cnn._transactionLevel = 0; // Make sure the transaction level is reset before returning
          329  +                if (throwError == true) throw new SQLiteException("No transaction is active on this connection");
          330  +                else return false;
          331  +            }
   107    332   
   108         -            //
   109         -            // NOTE: Everything should be fully disposed at this point.
   110         -            //
   111         -            disposed = true;
          333  +            return true;
   112    334           }
   113    335       }
   114         -    #endregion
   115         -
   116         -    ///////////////////////////////////////////////////////////////////////////////////////////////
   117         -
   118         -    /// <summary>
   119         -    /// Commits the current transaction.
   120         -    /// </summary>
   121         -    public override void Commit()
   122         -    {
   123         -      CheckDisposed();
   124         -      SQLiteConnection.Check(_cnn);
   125         -      IsValid(true);
   126         -
   127         -      if (_cnn._transactionLevel - 1 == 0)
   128         -      {
   129         -        using (SQLiteCommand cmd = _cnn.CreateCommand())
   130         -        {
   131         -          cmd.CommandText = "COMMIT";
   132         -          cmd.ExecuteNonQuery();
   133         -        }
   134         -      }
   135         -      _cnn._transactionLevel--;
   136         -      _cnn = null;
   137         -    }
   138         -
   139         -    /// <summary>
   140         -    /// Returns the underlying connection to which this transaction applies.
   141         -    /// </summary>
   142         -    public new SQLiteConnection Connection
   143         -    {
   144         -      get { CheckDisposed(); return _cnn; }
   145         -    }
   146         -
   147         -    /// <summary>
   148         -    /// Forwards to the local Connection property
   149         -    /// </summary>
   150         -    protected override DbConnection DbConnection
   151         -    {
   152         -      get { return Connection; }
   153         -    }
   154         -
   155         -    /// <summary>
   156         -    /// Gets the isolation level of the transaction.  SQLite only supports Serializable transactions.
   157         -    /// </summary>
   158         -    public override IsolationLevel IsolationLevel
   159         -    {
   160         -      get { CheckDisposed(); return _level; }
   161         -    }
   162         -
   163         -    /// <summary>
   164         -    /// Rolls back the active transaction.
   165         -    /// </summary>
   166         -    public override void Rollback()
   167         -    {
   168         -      CheckDisposed();
   169         -      SQLiteConnection.Check(_cnn);
   170         -      IsValid(true);
   171         -      IssueRollback(true);
   172         -    }
   173         -
   174         -    internal void IssueRollback(bool throwError)
   175         -    {
   176         -      SQLiteConnection cnn = Interlocked.Exchange(ref _cnn, null);
   177         -
   178         -      if (cnn != null)
   179         -      {
   180         -        if (_cnn._transactionLevel - 1 == 0)
   181         -        {
   182         -          try
   183         -          {
   184         -            using (SQLiteCommand cmd = cnn.CreateCommand())
   185         -            {
   186         -              cmd.CommandText = "ROLLBACK";
   187         -              cmd.ExecuteNonQuery();
   188         -            }
   189         -          }
   190         -          catch
   191         -          {
   192         -            if (throwError)
   193         -              throw;
   194         -          }
   195         -          cnn._transactionLevel--;
   196         -        }
   197         -        else
   198         -        {
   199         -          cnn._transactionLevel--;
   200         -
   201         -          if (throwError)
   202         -            throw new SQLiteException("Cannot rollback a nested transaction");
   203         -        }
   204         -      }
   205         -    }
   206         -
   207         -    internal bool IsValid(bool throwError)
   208         -    {
   209         -      if (_cnn == null)
   210         -      {
   211         -        if (throwError == true) throw new ArgumentNullException("No connection associated with this transaction");
   212         -        else return false;
   213         -      }
   214         -
   215         -      if (_cnn._version != _version)
   216         -      {
   217         -        if (throwError == true) throw new SQLiteException("The connection was closed and re-opened, changes were already rolled back");
   218         -        else return false;
   219         -      }
   220         -      if (_cnn.State != ConnectionState.Open)
   221         -      {
   222         -        if (throwError == true) throw new SQLiteException("Connection was closed");
   223         -        else return false;
   224         -      }
   225         -
   226         -      if (_cnn._transactionLevel == 0 || _cnn._sql.AutoCommit == true)
   227         -      {
   228         -        _cnn._transactionLevel = 0; // Make sure the transaction level is reset before returning
   229         -        if (throwError == true) throw new SQLiteException("No transaction is active on this connection");
   230         -        else return false;
   231         -      }
   232         -
   233         -      return true;
   234         -    }
   235         -  }
   236    336   }

Added Tests/tkt-1f7bfff467.eagle.

            1  +###############################################################################
            2  +#
            3  +# tkt-1f7bfff467.eagle --
            4  +#
            5  +# Written by Joe Mistachkin.
            6  +# Released to the public domain, use at your own risk!
            7  +#
            8  +###############################################################################
            9  +
           10  +package require Eagle
           11  +package require Eagle.Library
           12  +package require Eagle.Test
           13  +
           14  +runTestPrologue
           15  +
           16  +###############################################################################
           17  +
           18  +package require System.Data.SQLite.Test
           19  +runSQLiteTestPrologue
           20  +
           21  +###############################################################################
           22  +
           23  +runTest {test tkt-1f7bfff467-1.1 {rollback nested transaction} -setup {
           24  +  setupDb [set fileName tkt-1f7bfff467-1.1.db]
           25  +} -body {
           26  +  sql execute $db {
           27  +    CREATE TABLE t1(x);
           28  +    INSERT INTO t1(x) VALUES(1);
           29  +    INSERT INTO t1(x) VALUES(2);
           30  +    INSERT INTO t1(x) VALUES(3);
           31  +  }
           32  +
           33  +  set result [list]
           34  +
           35  +  set transaction(1) [sql transaction begin $db]
           36  +
           37  +  lappend result [sql execute -transaction $transaction(1) \
           38  +      -execute scalar $db "SELECT COUNT(*) FROM t1;"]
           39  +
           40  +  sql execute -transaction $transaction(1) $db \
           41  +      "INSERT INTO t1(x) VALUES(4);"
           42  +
           43  +  lappend result [sql execute -transaction $transaction(1) \
           44  +      -execute reader -format list $db \
           45  +      "SELECT x FROM t1 ORDER BY x;"]
           46  +
           47  +  set transaction(2) [sql transaction begin $db]
           48  +
           49  +  sql execute -transaction $transaction(2) $db \
           50  +      "DELETE FROM t1 WHERE x = 2;"
           51  +
           52  +  lappend result [sql execute -transaction $transaction(2) \
           53  +      -execute reader -format list $db \
           54  +      "SELECT x FROM t1 ORDER BY x;"]
           55  +
           56  +  sql transaction rollback $transaction(2)
           57  +
           58  +  sql execute -transaction $transaction(1) $db \
           59  +      "UPDATE t1 SET x = 9 WHERE x = 3;"
           60  +
           61  +  sql transaction commit $transaction(1)
           62  +
           63  +  lappend result [sql execute \
           64  +      -execute reader -format list $db \
           65  +      "SELECT x FROM t1 ORDER BY x;"]
           66  +
           67  +  set result
           68  +} -cleanup {
           69  +  cleanupDb $fileName
           70  +
           71  +  unset -nocomplain result transaction db fileName
           72  +} -constraints {eagle command.object monoBug28 monoBug211 command.sql\
           73  +compile.DATA SQLite System.Data.SQLite} -result {3 {1 2 3 4} {1 3 4} {1 2 4 9}}}
           74  +
           75  +###############################################################################
           76  +
           77  +runSQLiteTestEpilogue
           78  +runTestEpilogue