System.Data.SQLite

Check-in [712e256f4f]
Login

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

Overview
Comment:Use savepoints to implement nested transaction support.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | tkt-1f7bfff467
Files: files | file ages | folders
SHA1: 712e256f4f0433abc15cbbda5495c30fa6ec3686
User & Date: mistachkin 2016-10-28 20:09:33.628
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
Unified Diff Show Whitespace Changes Patch
Changes to System.Data.SQLite/SQLiteTransaction.cs.
︙︙
8
9
10
11
12
13
14


15
16
17
18
19
20
21

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








>
>








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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
︙︙
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

      _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;
        }
      }
    }

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









>
>
|






|

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








>








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

      _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;
        }
      }
    }

    ///////////////////////////////////////////////////////////////////////////////////////////////
︙︙
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

    /// </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;
      }








|
>
>



|


|



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









>
>








>
>







>
>












>
>
>
>
>
>
>
>
>






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





|
>
>








<

<
|

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

|
>

>
>
>
>
>
>
>
>
>








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

    /// </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;
      }
︙︙
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