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 Unified Diffs Ignore Whitespace Patch

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

3
4
5
6
7
8
9
10
11
12
13
14


15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

59
60
61








62

63











64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95






















































































































































96
97
98




99
100
101
102














103
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140


141
142
143
144
145
146
147
148

149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 *
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace System.Data.SQLite
{
  using System;
  using System.Data;
  using System.Data.Common;
  using System.Threading;



  /// <summary>
  /// SQLite implementation of DbTransaction.
  /// </summary>
  public sealed class SQLiteTransaction : DbTransaction
  {
    /// <summary>
    /// The connection to which this transaction is bound
    /// </summary>
    internal SQLiteConnection _cnn;
    internal int _version; // Matches the version of the connection
    private IsolationLevel _level;

    /// <summary>
    /// Constructs the transaction object, binding it to the supplied connection
    /// </summary>
    /// <param name="connection">The connection to open a transaction on</param>
    /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
    internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock)
    {
      _cnn = connection;
      _version = _cnn._version;

      _level = (deferredLock == true) ?
          SQLiteConnection.DeferredIsolationLevel :
          SQLiteConnection.ImmediateIsolationLevel;



      if (_cnn._transactionLevel++ == 0)
      {
        try
        {
          using (SQLiteCommand cmd = _cnn.CreateCommand())
          {
            if (!deferredLock)
              cmd.CommandText = "BEGIN IMMEDIATE";
            else
              cmd.CommandText = "BEGIN";

            cmd.ExecuteNonQuery();
          }
        }
        catch (SQLiteException)
        {
          _cnn._transactionLevel--;
          _cnn = null;

          throw;
        }
      }








    }













    ///////////////////////////////////////////////////////////////////////////////////////////////

    #region IDisposable "Pattern" Members
    private bool disposed;
    private void CheckDisposed() /* throw */
    {
#if THROW_ON_DISPOSED
        if (disposed)
            throw new ObjectDisposedException(typeof(SQLiteTransaction).Name);
#endif
    }

    ///////////////////////////////////////////////////////////////////////////////////////////////

    /// <summary>
    /// Disposes the transaction.  If it is currently active, any changes are rolled back.
    /// </summary>
    protected override void Dispose(bool disposing)
    {
        try
        {
            if (!disposed)
            {
                if (disposing)
                {
                    ////////////////////////////////////
                    // dispose managed resources here...
                    ////////////////////////////////////

                    if (IsValid(false))
                    {
                        IssueRollback(false);






















































































































































                    }
                }





                //////////////////////////////////////
                // release unmanaged resources here...
                //////////////////////////////////////
            }














        }
................................................................................
        finally
        {
            base.Dispose(disposing);

            //
            // NOTE: Everything should be fully disposed at this point.
            //
            disposed = true;
        }
    }
    #endregion

    ///////////////////////////////////////////////////////////////////////////////////////////////

    /// <summary>
    /// Commits the current transaction.

    /// </summary>
    public override void Commit()
    {
      CheckDisposed();
      SQLiteConnection.Check(_cnn);
      IsValid(true);

      if (_cnn._transactionLevel - 1 == 0)
      {
        using (SQLiteCommand cmd = _cnn.CreateCommand())
        {
          cmd.CommandText = "COMMIT";
          cmd.ExecuteNonQuery();
        }
      }
      _cnn._transactionLevel--;
      _cnn = null;
    }

    /// <summary>
    /// Returns the underlying connection to which this transaction applies.


    /// </summary>
    public new SQLiteConnection Connection
    {
      get { CheckDisposed(); return _cnn; }
    }

    /// <summary>
    /// Forwards to the local Connection property

    /// </summary>
    protected override DbConnection DbConnection
    {
      get { return Connection; }
    }

    /// <summary>
    /// Gets the isolation level of the transaction.  SQLite only supports Serializable transactions.
    /// </summary>
    public override IsolationLevel IsolationLevel
    {
      get { CheckDisposed(); return _level; }
    }

    /// <summary>
    /// Rolls back the active transaction.
    /// </summary>
    public override void Rollback()
    {
      CheckDisposed();
      SQLiteConnection.Check(_cnn);
      IsValid(true);
      IssueRollback(true);
    }

    internal void IssueRollback(bool throwError)
    {
      SQLiteConnection cnn = Interlocked.Exchange(ref _cnn, null);

      if (cnn != null)
      {
        if (_cnn._transactionLevel - 1 == 0)
        {
          try
          {
            using (SQLiteCommand cmd = cnn.CreateCommand())
            {
              cmd.CommandText = "ROLLBACK";
              cmd.ExecuteNonQuery();
            }
          }
          catch
          {
            if (throwError)
              throw;
          }
          cnn._transactionLevel--;
        }
        else
        {
          cnn._transactionLevel--;

          if (throwError)
            throw new SQLiteException("Cannot rollback a nested transaction");
        }
      }
    }

    internal bool IsValid(bool throwError)
    {
      if (_cnn == null)
      {
        if (throwError == true) throw new ArgumentNullException("No connection associated with this transaction");
        else return false;
      }

      if (_cnn._version != _version)
      {
        if (throwError == true) throw new SQLiteException("The connection was closed and re-opened, changes were already rolled back");
        else return false;
      }
      if (_cnn.State != ConnectionState.Open)
      {
        if (throwError == true) throw new SQLiteException("Connection was closed");
        else return false;
      }

      if (_cnn._transactionLevel == 0 || _cnn._sql.AutoCommit == true)
      {
        _cnn._transactionLevel = 0; // Make sure the transaction level is reset before returning
        if (throwError == true) throw new SQLiteException("No transaction is active on this connection");
        else return false;
      }

      return true;
    }
  }
}







|
|
|
|

>
>
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|

|
|
|

>
>
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
>
|
|
|
>
>
>
>
>
>
>
>
|
>
|
>
>
>
>
>
>
>
>
>
>
>
|

|
|
|
|

|
|

|

|

|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



>
>
>
>
|
<
<
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>

 
<
|
<
<
<
<
<
<
<
<
<
<
|

|
<
>
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
>
>
|
<
<
<
<
<
|
<
>
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|

|
|
|

3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278


279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
...

295










296
297
298

299
300




















301
302
303





304

305
306

























































307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 *
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace System.Data.SQLite
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Threading;

    ///////////////////////////////////////////////////////////////////////////////////////////////

    /// <summary>
    /// SQLite implementation of DbTransaction.
    /// </summary>
    public sealed class SQLiteTransaction : DbTransaction
    {
        /// <summary>
        /// The connection to which this transaction is bound
        /// </summary>
        internal SQLiteConnection _cnn;
        internal int _version; // Matches the version of the connection
        private IsolationLevel _level;

        /// <summary>
        /// Constructs the transaction object, binding it to the supplied connection
        /// </summary>
        /// <param name="connection">The connection to open a transaction on</param>
        /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
        internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock)
        {
            _cnn = connection;
            _version = _cnn._version;

            _level = (deferredLock == true) ?
                SQLiteConnection.DeferredIsolationLevel :
                SQLiteConnection.ImmediateIsolationLevel;

            int level;

            if ((level = _cnn._transactionLevel++) == 0)
            {
                try
                {
                    using (SQLiteCommand cmd = _cnn.CreateCommand())
                    {
                        if (!deferredLock)
                            cmd.CommandText = "BEGIN IMMEDIATE;";
                        else
                            cmd.CommandText = "BEGIN;";

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SQLiteException)
                {
                    _cnn._transactionLevel--;
                    _cnn = null;

                    throw;
                }
            }
            else
            {
                try
                {
                    using (SQLiteCommand cmd = _cnn.CreateCommand())
                    {
                        cmd.CommandText = String.Format(
                            "SAVEPOINT {0};", GetSavePointName(level));

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SQLiteException)
                {
                    _cnn._transactionLevel--;
                    _cnn = null;

                    throw;
                }
            }
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////

        #region IDisposable "Pattern" Members
        private bool disposed;
        private void CheckDisposed() /* throw */
        {
#if THROW_ON_DISPOSED
            if (disposed)
                throw new ObjectDisposedException(typeof(SQLiteTransaction).Name);
#endif
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Disposes the transaction.  If it is currently active, any changes are rolled back.
        /// </summary>
        protected override void Dispose(bool disposing)
        {
            try
            {
                if (!disposed)
                {
                    if (disposing)
                    {
                        ////////////////////////////////////
                        // dispose managed resources here...
                        ////////////////////////////////////

                        if (IsValid(false))
                        {
                            IssueRollback(false);
                        }
                    }

                    //////////////////////////////////////
                    // release unmanaged resources here...
                    //////////////////////////////////////
                }
            }
            finally
            {
                base.Dispose(disposing);

                //
                // NOTE: Everything should be fully disposed at this point.
                //
                disposed = true;
            }
        }
        #endregion

        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Commits the current transaction.
        /// </summary>
        public override void Commit()
        {
            CheckDisposed();
            SQLiteConnection.Check(_cnn);
            IsValid(true);

            int level = _cnn._transactionLevel;

            if (level - 1 == 0)
            {
                using (SQLiteCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = "COMMIT;";
                    cmd.ExecuteNonQuery();
                }

                _cnn._transactionLevel--;
                _cnn = null;
            }
            else
            {
                using (SQLiteCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = String.Format(
                        "RELEASE {0};", GetSavePointName(level - 1));

                    cmd.ExecuteNonQuery();
                }

                _cnn._transactionLevel--;
            }
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Returns the underlying connection to which this transaction applies.
        /// </summary>
        public new SQLiteConnection Connection
        {
            get { CheckDisposed(); return _cnn; }
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Forwards to the local Connection property
        /// </summary>
        protected override DbConnection DbConnection
        {
            get { return Connection; }
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Gets the isolation level of the transaction.  SQLite only supports Serializable transactions.
        /// </summary>
        public override IsolationLevel IsolationLevel
        {
            get { CheckDisposed(); return _level; }
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Rolls back the active transaction.
        /// </summary>
        public override void Rollback()
        {
            CheckDisposed();
            SQLiteConnection.Check(_cnn);
            IsValid(true);
            IssueRollback(true);
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>
        /// Issue a ROLLBACK command against the database connection,
        /// optionally re-throwing any caught exception.
        /// </summary>
        /// <param name="throwError">
        /// Non-zero to re-throw caught exceptions.
        /// </param>
        internal void IssueRollback(bool throwError)
        {
            SQLiteConnection cnn = Interlocked.Exchange(ref _cnn, null);

            if (cnn != null)
            {
                int level = cnn._transactionLevel;

                if (level - 1 == 0)
                {
                    try
                    {
                        using (SQLiteCommand cmd = cnn.CreateCommand())
                        {
                            cmd.CommandText = "ROLLBACK;";
                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch
                    {
                        if (throwError)
                            throw;
                    }
                }
                else
                {
                    try
                    {
                        using (SQLiteCommand cmd = cnn.CreateCommand())
                        {
                            cmd.CommandText = String.Format(
                                "ROLLBACK TO {0};", GetSavePointName(level - 1));

                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch
                    {
                        if (throwError)
                            throw;
                    }
                }

                cnn._transactionLevel--;
            }
        }

        ///////////////////////////////////////////////////////////////////////////////////////////////



        /// <summary>
        /// Constructs the name of a new or existing savepoint.
        /// </summary>
        /// <param name="level">
        /// The transaction level associated with the connection.
        /// </param>
        /// <returns>
        /// The name of the savepoint -OR- null if it cannot be constructed.
        /// </returns>
        private static string GetSavePointName(
            int level
            )
        {
            return String.Format("sqlite_dotnet_savepoint_{0}", level);
        }
................................................................................












        ///////////////////////////////////////////////////////////////////////////////////////////////

        /// <summary>

        /// Checks the state of this transaction, optionally throwing an exception if a state inconsistency is found.
        /// </summary>




















        /// <param name="throwError">
        /// Non-zero to throw an exception if a state inconsistency is found.
        /// </param>





        /// <returns>

        /// Non-zero if this transaction is valid; otherwise, false.
        /// </returns>

























































        internal bool IsValid(bool throwError)
        {
            if (_cnn == null)
            {
                if (throwError == true) throw new ArgumentNullException("No connection associated with this transaction");
                else return false;
            }

            if (_cnn._version != _version)
            {
                if (throwError == true) throw new SQLiteException("The connection was closed and re-opened, changes were already rolled back");
                else return false;
            }
            if (_cnn.State != ConnectionState.Open)
            {
                if (throwError == true) throw new SQLiteException("Connection was closed");
                else return false;
            }

            if (_cnn._transactionLevel == 0 || _cnn._sql.AutoCommit == true)
            {
                _cnn._transactionLevel = 0; // Make sure the transaction level is reset before returning
                if (throwError == true) throw new SQLiteException("No transaction is active on this connection");
                else return false;
            }

            return true;
        }
    }
}

Added Tests/tkt-1f7bfff467.eagle.





























































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
###############################################################################
#
# tkt-1f7bfff467.eagle --
#
# Written by Joe Mistachkin.
# Released to the public domain, use at your own risk!
#
###############################################################################

package require Eagle
package require Eagle.Library
package require Eagle.Test

runTestPrologue

###############################################################################

package require System.Data.SQLite.Test
runSQLiteTestPrologue

###############################################################################

runTest {test tkt-1f7bfff467-1.1 {rollback nested transaction} -setup {
  setupDb [set fileName tkt-1f7bfff467-1.1.db]
} -body {
  sql execute $db {
    CREATE TABLE t1(x);
    INSERT INTO t1(x) VALUES(1);
    INSERT INTO t1(x) VALUES(2);
    INSERT INTO t1(x) VALUES(3);
  }

  set result [list]

  set transaction(1) [sql transaction begin $db]

  lappend result [sql execute -transaction $transaction(1) \
      -execute scalar $db "SELECT COUNT(*) FROM t1;"]

  sql execute -transaction $transaction(1) $db \
      "INSERT INTO t1(x) VALUES(4);"

  lappend result [sql execute -transaction $transaction(1) \
      -execute reader -format list $db \
      "SELECT x FROM t1 ORDER BY x;"]

  set transaction(2) [sql transaction begin $db]

  sql execute -transaction $transaction(2) $db \
      "DELETE FROM t1 WHERE x = 2;"

  lappend result [sql execute -transaction $transaction(2) \
      -execute reader -format list $db \
      "SELECT x FROM t1 ORDER BY x;"]

  sql transaction rollback $transaction(2)

  sql execute -transaction $transaction(1) $db \
      "UPDATE t1 SET x = 9 WHERE x = 3;"

  sql transaction commit $transaction(1)

  lappend result [sql execute \
      -execute reader -format list $db \
      "SELECT x FROM t1 ORDER BY x;"]

  set result
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result transaction db fileName
} -constraints {eagle command.object monoBug28 monoBug211 command.sql\
compile.DATA SQLite System.Data.SQLite} -result {3 {1 2 3 4} {1 3 4} {1 2 4 9}}}

###############################################################################

runSQLiteTestEpilogue
runTestEpilogue