sqlite - savePoint无效，应该是调用SaveTransactionPoint的结果

 database.RunInTransaction(() =>
      {
            if (dbVersion < DatabaseConstants.DATABASE_VERSION)
            {
                OnUpgrade();
              }
    });
        database.Commit();
    }


  public void onUpgrade(){
     //inserting list of person
     database.RunInTransaction(() =>
           {
            //insert(TableNamePerson,PersonData)
            });
      database.Commit();


     database.RunInTransaction(() =>
        {
          //insert(TableNameContacts,ContactData)
       });
       database.Commit();
    }

使用嵌套事务时，出现以下异常：

savePoint无效，应该是调用SaveTransactionPoint的结果

最佳答案

根据我在文档和代码中所看到的，它可能是预期的行为。为Action指定的RunInTransaction被整体封装在事务中。由于SQLite一次仅支持一个事务，因此如果不存在则创建一个新事务，否则将在已经运行的事务中创建一个保存点。

也就是说，您不必调用commit，也不允许这样做。 RunInTransaction会照顾您的。这对于一个块来说可以很好地工作，但是与嵌套的RunInTransaction块一样，在代码中的行为也不一致。我认为这是一个错误。

在例外情况下，整个事务将回滚并因此终止。这意味着即使那些成功完成的块也将被回滚！显式或隐式BEGIN之后的所有语句都消失了。即使您捕获了内部异常，所有外部RunInTransaction块也会引发ArgumentExceptions：

savePoint is not valid, and should be the result of a call to SaveTransactionPoint

如果要控制更多，则需要使用显式保存点和RollbackTo而不是Rollback。

我创建了一个单元测试来说明会发生什么：

[Test]
public void InsertItemIntoTable()
{
    using (var connection = Container.Resolve<IDatabase>().GetConnection())
    {
        var item1 = new Item { Id = 1, Description = "Test 1", Text = "Text for test 1" };
        var item2 = new Item { Id = 2, Description = "Test 2", Text = "Text for test 2" };
        var countAtStart = connection.Query<Item>("SELECT * FROM Item").Count;

        connection.RunInTransaction(() => // transaction started
        {
            var saveTransactionPoint = connection.SaveTransactionPoint();
            connection.Insert(item2);
            // would fail as a commit would finish the transaction inside the action:
            // connection.Commit();

            // works as the transaction does not yet end
            connection.RollbackTo(saveTransactionPoint);
            Assert.IsTrue(connection.IsInTransaction);
        });
        Assert.IsFalse(connection.IsInTransaction);

        try
        {
            connection.RunInTransaction(() => // transaction started
            {
                connection.Insert(item1);
                var countAfter1stInsert = connection.Query<Item>("SELECT * FROM Item").Count;
                Assert.AreEqual(countAtStart + 1, countAfter1stInsert);
                connection.RunInTransaction(() => { connection.Insert(item2); });
                var countAfter2ndInsert = connection.Query<Item>("SELECT * FROM Item").Count;
                Assert.AreEqual(countAtStart + 2, countAfter2ndInsert);
                // bad SQL statement provokes an exception: no such table: bar.
                try
                {
                    connection.RunInTransaction(() => // new save point within running transaction
                    {
                        connection.Execute("SELECT foo FROM bar", "will throw exception");
                    });
                }
                catch (Exception e)
                {
                    // the whole transaction was rolled back already
                    Assert.IsFalse(connection.IsInTransaction);
                    // that is why the outer block will fail next
                }
            });
        }
        catch (Exception e)
        {
            // outer RunInTransaction could not release its own save point and crashes with:
            // "savePoint is not valid, and should be the result of a call to SaveTransactionPoint"
        }

        var countAfterRollback = connection.Query<Item>("SELECT * FROM Item").Count;
        Assert.AreEqual(countAtStart, countAfterRollback);

        Assert.IsFalse(connection.IsInTransaction);
        // new transaction point start a deferred transaction as no transaction is running
        var point1 = connection.SaveTransactionPoint();
        Assert.IsTrue(connection.IsInTransaction);
        connection.Insert(item1);
        var point2 = connection.SaveTransactionPoint();
        connection.Insert(item2);
        var point3 = connection.SaveTransactionPoint();
        connection.Execute("INSERT INTO 'Item'('Id','Text','Description') VALUES (100,'Test 100',NULL);");
        connection.RollbackTo(point3);
        connection.RollbackTo(point2);
        // will commit the first insert i.e. item1, which implictily began a transaction
        connection.Commit();
        Assert.IsFalse(connection.IsInTransaction);
        var afterFinalRollback = connection.Query<Item>("SELECT * FROM Item").Count;
        // thus item1 has made it to the database
        Assert.AreEqual(countAtStart + 1, afterFinalRollback);
        // but not for ever ;)
        connection.Execute("delete from item where id > 0");
    }

}