6

My SQLite-based application currently uses transactions - both for being able to rollback and for improving performance. I'm considering replacing all transactions with savepoints. The reason is that the application is multi-threaded (yes, sqlite is configured to be thread-safe), and in some cases a transaction might get started by two threads in the same time (on the same db).

  1. It there a reason NOT to do it?
  2. Are there any pitfalls I need to be aware of?
  3. Do I just replace BEGIN, COMMIT, ROLLBACK with SAVEPOINT xyz, RELEASE SAVEPOINT xyz, ROLLBACK TO SAVEPOINT xyz?
tijko
  • 7,599
  • 11
  • 44
  • 64
noamtm
  • 12,435
  • 15
  • 71
  • 107
  • 1
    I used _savepoints inside a transaction to implement nested transactions_ but **what your suggesting is nonsense**. savepoints don't work that way. – Dan D. Mar 12 '12 at 12:24
  • FWIW, I've had weird issues just replacing "BEGIN, COMMIT, ROLLBACK with SAVEPOINT xyz, RELEASE SAVEPOINT xyz, ROLLBACK TO SAVEPOINT xyz" like you say. In simple scenarios everything was fine. But my scenario is complex and involves multi-threading. I wanted the "name" feature. I had no errors, everything seemed ok, but some changes (delete, etc.) were not written in the database after close (although there were visible with the database open!). I lost quite a few hair on this, and ended up back with standard BEGIN, COMMIT and ROLLBACK. – Simon Mourier Jan 17 '20 at 11:04

1 Answers1

7
    It there a reason NOT to do it?

Yes. It won't solve any of the problems that you outlined. Save points are primarily used to be able to do partial rollbacks of data. The outer transaction or savepoint is what actually is committed. Nothing is really fully saved until that outermost savepoint is released thus updating the DB. You are right back back to the same problem that you have with standard transactions.

    Are there any pitfalls I need to be aware of?

Yes. Transactions or savepoints in a multithreaded application can deadlock fairly easily if you are update the same data in two different threads which I assume is the heart of the matter. There is no difference between the two in this regard. You should be aware of what you are updating in each thread and synchronize accordingly.

In short, unless you have the need to do partial transaction rollback, savepoints really wont give you much (other than the fact that they are named.)

There is no silver bullet here. It sounds like you need to do a serious analyses of your application and the data that may be updated in multiple threads and add some synchronization in you application if needed.

Devin
  • 559
  • 4
  • 14