System.Data.SQLite
Check-in [0898ed0bbf]
Not logged in

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

Overview
Comment:Fix commit of nested transactions that are out-of-order. Use a sequence number to construct SAVEPOINT names, not a random number.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-1f7bfff467
Files: files | file ages | folders
SHA1:0898ed0bbfa785b3549796b5c6a4897479cf243d
User & Date: mistachkin 2016-10-30 20:31:02
Context
2016-10-30
20:38
Add another test. check-in: cda90ad6d1 user: mistachkin tags: tkt-1f7bfff467
20:31
Fix commit of nested transactions that are out-of-order. Use a sequence number to construct SAVEPOINT names, not a random number. check-in: 0898ed0bbf user: mistachkin tags: tkt-1f7bfff467
03:27
Do not try to rollback a nested transaction after it has been committed. check-in: 2bed8a7ba9 user: mistachkin tags: tkt-1f7bfff467
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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

1441
1442
1443
1444
1445
1446
1447






1448
1449
1450
1451
1452
1453
1454
....
2841
2842
2843
2844
2845
2846
2847

2848
2849
2850
2851
2852
2853
2854
....
2858
2859
2860
2861
2862
2863
2864

2865
2866
2867
2868
2869
2870
2871
    private string _connectionString;

    /// <summary>
    /// Nesting level of the transactions open on the connection
    /// </summary>
    internal int _transactionLevel;







    /// <summary>
    /// If this flag is non-zero, the <see cref="Dispose()" /> method will have
    /// no effect; however, the <see cref="Close" /> method will continue to
    /// behave as normal.
    /// </summary>
    internal bool _noDispose;

................................................................................
        {
          // If the connection is enlisted in a transaction scope and the scope is still active,
          // we cannot truly shut down this connection until the scope has completed.  Therefore make a
          // hidden connection temporarily to hold open the connection until the scope has completed.
          SQLiteConnection cnn = new SQLiteConnection();
          cnn._sql = _sql;
          cnn._transactionLevel = _transactionLevel;

          cnn._enlistment = _enlistment;
          cnn._connectionState = _connectionState;
          cnn._version = _version;

          cnn._enlistment._transaction._cnn = cnn;
          cnn._enlistment._disposeConnection = true;

................................................................................
#endif
        if (_sql != null)
        {
          _sql.Close(!_disposing);
          _sql = null;
        }
        _transactionLevel = 0;

      }

      StateChangeEventArgs eventArgs = null;
      OnStateChange(ConnectionState.Closed, ref eventArgs);

      OnChanged(this, new ConnectionEventArgs(
          SQLiteConnectionEventType.Closed, eventArgs, null, null, null,







>
>
>
>
>
>







 







>







 







>







1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
....
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
....
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
    private string _connectionString;

    /// <summary>
    /// Nesting level of the transactions open on the connection
    /// </summary>
    internal int _transactionLevel;

    /// <summary>
    /// Transaction counter for the connection.  Currently, this is only used
    /// to build SAVEPOINT names.
    /// </summary>
    internal int _transactionSequence;

    /// <summary>
    /// If this flag is non-zero, the <see cref="Dispose()" /> method will have
    /// no effect; however, the <see cref="Close" /> method will continue to
    /// behave as normal.
    /// </summary>
    internal bool _noDispose;

................................................................................
        {
          // If the connection is enlisted in a transaction scope and the scope is still active,
          // we cannot truly shut down this connection until the scope has completed.  Therefore make a
          // hidden connection temporarily to hold open the connection until the scope has completed.
          SQLiteConnection cnn = new SQLiteConnection();
          cnn._sql = _sql;
          cnn._transactionLevel = _transactionLevel;
          cnn._transactionSequence = _transactionSequence;
          cnn._enlistment = _enlistment;
          cnn._connectionState = _connectionState;
          cnn._version = _version;

          cnn._enlistment._transaction._cnn = cnn;
          cnn._enlistment._disposeConnection = true;

................................................................................
#endif
        if (_sql != null)
        {
          _sql.Close(!_disposing);
          _sql = null;
        }
        _transactionLevel = 0;
        _transactionSequence = 0;
      }

      StateChangeEventArgs eventArgs = null;
      OnStateChange(ConnectionState.Closed, ref eventArgs);

      OnChanged(this, new ConnectionEventArgs(
          SQLiteConnectionEventType.Closed, eventArgs, null, null, null,

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

31
32
33
34
35
36
37






38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
..
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
..
88
89
90
91
92
93
94
95

96
97


98
99
100
101
102
103
104
...
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
...
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
        private int _version;

        /// <summary>
        /// The isolation level for this transaction.
        /// </summary>
        private IsolationLevel _level;







        /// <summary>
        /// The SAVEPOINT names for each transaction level.
        /// </summary>
        private Dictionary<int, string> _savePointNames;

        /// <summary>
        /// Random number generator used when creating new SAVEPOINT names.
        /// </summary>
        private Random _random;

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

        /// <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>
................................................................................
            _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;

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


                        cmd.ExecuteNonQuery();


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

................................................................................
        /// </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();
                }



            }
            else
            {
                using (SQLiteCommand cmd = _cnn.CreateCommand())
                {


                    cmd.CommandText = String.Format(
                        "RELEASE {0};", GetSavePointName(level - 1));


                    cmd.ExecuteNonQuery();
                }
            }

            _cnn._transactionLevel--;
            _cnn = null;

        }

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

        /// <summary>
        /// Returns the underlying connection to which this transaction applies.
        /// </summary>
................................................................................
        /// </param>
        private 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 string GetSavePointName(
            int level
            )
        {
            if (_savePointNames == null)
                _savePointNames = new Dictionary<int, string>();

            string name;

            if (!_savePointNames.TryGetValue(level, out name))
            {
                if (_random == null)
                    _random = new Random();

                name = String.Format(
                    "sqlite_dotnet_savepoint_{0}_{1}", level, _random.Next());


                _savePointNames[level] = name;
            }

            return name;
        }

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








>
>
>
>
>
>





<
<
<
<
<







 







|

|











>
>







 







|
>


>
>







 







|
<
<






>
>
>





>
>

|
>



|
<
|
|
>







 







|
<
<








>
>













>
>

|
>



>
>







<
<








|






|







|

|
<


|
>

|







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
..
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
..
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
...
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
...
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
337
338
339
340
341
342
343
344
345
        private int _version;

        /// <summary>
        /// The isolation level for this transaction.
        /// </summary>
        private IsolationLevel _level;

        /// <summary>
        /// The original transaction level for the associated connection
        /// when this transaction was created (i.e. begun).
        /// </summary>
        private int _beginLevel;

        /// <summary>
        /// The SAVEPOINT names for each transaction level.
        /// </summary>
        private Dictionary<int, string> _savePointNames;






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

        /// <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>
................................................................................
            _cnn = connection;
            _version = _cnn._version;

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

            int transactionLevel;

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

                        cmd.ExecuteNonQuery();

                        _beginLevel = transactionLevel;
                    }
                }
                catch (SQLiteException)
                {
                    _cnn._transactionLevel--;
                    _cnn = null;

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

                        cmd.ExecuteNonQuery();

                        _beginLevel = transactionLevel;
                    }
                }
                catch (SQLiteException)
                {
                    _cnn._transactionLevel--;
                    _cnn = null;

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

            if (_beginLevel == 0)


            {
                using (SQLiteCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = "COMMIT;";
                    cmd.ExecuteNonQuery();
                }

                _cnn._transactionLevel = 0;
                _cnn = null;
            }
            else
            {
                using (SQLiteCommand cmd = _cnn.CreateCommand())
                {
                    int transactionLevel = _cnn._transactionLevel;

                    cmd.CommandText = String.Format(
                        "RELEASE {0};", GetSavePointName(
                        transactionLevel - 1));

                    cmd.ExecuteNonQuery();
                }


                _cnn._transactionLevel--;
                _cnn = null;
            }
        }

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

        /// <summary>
        /// Returns the underlying connection to which this transaction applies.
        /// </summary>
................................................................................
        /// </param>
        private void IssueRollback(bool throwError)
        {
            SQLiteConnection cnn = Interlocked.Exchange(ref _cnn, null);

            if (cnn != null)
            {
                if (_beginLevel == 0)


                {
                    try
                    {
                        using (SQLiteCommand cmd = cnn.CreateCommand())
                        {
                            cmd.CommandText = "ROLLBACK;";
                            cmd.ExecuteNonQuery();
                        }

                        cnn._transactionLevel = 0;
                    }
                    catch
                    {
                        if (throwError)
                            throw;
                    }
                }
                else
                {
                    try
                    {
                        using (SQLiteCommand cmd = cnn.CreateCommand())
                        {
                            int transactionLevel = cnn._transactionLevel;

                            cmd.CommandText = String.Format(
                                "ROLLBACK TO {0};", GetSavePointName(
                                transactionLevel - 1));

                            cmd.ExecuteNonQuery();
                        }

                        cnn._transactionLevel--;
                    }
                    catch
                    {
                        if (throwError)
                            throw;
                    }
                }


            }
        }

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

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

            string name;

            if (!_savePointNames.TryGetValue(transactionLevel, out name))
            {
                int sequence = ++_cnn._transactionSequence;


                name = String.Format(
                    "sqlite_dotnet_savepoint_{0}_{1}",
                    transactionLevel, sequence);

                _savePointNames[transactionLevel] = name;
            }

            return name;
        }

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

Changes to Tests/tkt-1f7bfff467.eagle.

65
66
67
68
69
70
71
72
73
74
75
76

















































































77
78
      "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







|
|



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


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
      "SELECT x FROM t1 ORDER BY x;"]

  set result
} -cleanup {
  cleanupDb $fileName

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

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

runTest {test tkt-1f7bfff467-1.2 {commit outer transaction} -setup {
  setupDb [set fileName tkt-1f7bfff467-1.2.db]
} -body {
  sql execute $db {
    CREATE TABLE t1(x);
    INSERT INTO t1(x) VALUES(1);
  }

  set sql(1) { \
    INSERT INTO t1(x) VALUES(2); \
  }

  set sql(2) { \
    INSERT INTO t1(x) VALUES(3); \
  }

  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;

    namespace _Dynamic${id}
    {
      public static class Test${id}
      {
        public static void Main()
        {
          using (SQLiteConnection connection = new SQLiteConnection(
              "Data Source=${dataSource};[getFlagsProperty]"))
          {
            connection.Open();

            using (SQLiteTransaction transaction1 =
                connection.BeginTransaction())
            {
              using (SQLiteCommand command1 = new SQLiteCommand("${sql(1)}",
                  connection))
              {
                command1.Transaction = transaction1;
                command1.ExecuteNonQuery();
              }

              using (SQLiteTransaction transaction2 =
                  connection.BeginTransaction())
              {
                using (SQLiteCommand command2 = new SQLiteCommand("${sql(2)}",
                    connection))
                {
                  command2.Transaction = transaction2;
                  command2.ExecuteNonQuery();

                  transaction1.Commit();
                }
              }
            }
          }
        }
      }
    }
  }] 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 \
      [sql execute -execute reader -format list $db \
          "SELECT x FROM t1 ORDER BY x;"]
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result results errors code sql dataSource id db fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite compileCSharp} -match regexp -result {^Ok\
System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\} \{1 2 3\}$}}

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

runSQLiteTestEpilogue
runTestEpilogue