Reply to thread

Message: <blockquote data-quote="MJGraf" data-source="post: 1031629" data-attributes="member: 17886">Ok, let me tell you a bit more about what I found out in the meantime. For me it is more like a test protocol - maybe that's boring for everyone else, but I need to write this down to make sure whoever touches the SQLiteDatabase in the future knows why we chose which setting...First of all, I realized that there is a bug in my code. Background: MP2 with SQLiteDatabase uses the MediaPortal.Database.SQLite.SQLiteTransaction class to implement transactions. Let's call this class the MP2-Transaction for now. This class wraps around the System.Data.SQLite.SQLiteTransaction class - I will call this class SQLiteTransaction in the following. Now the code in the MediaLibrary gracefully calls Dispose() on every MP2-Transaction it uses. But the implementation of the MP2-Transaction's Dispose() method did not call Dispose() on the SQLiteTransaction it contained. Now why did it work anyway?When you call Commit() or Rollback() on the MP2-Transaction, it calls Commit() or Rollback on the contained SQLiteTransaction. By browsing through the source code of SQLiteTransaction I found out that by calling Commit() or Rollback() on the SQLiteTransaction it more or less cleans up all the necessary resources so that an additional call to Dispose() is not absolutely necessary.But why do we have to call Dispose() anyway? Well, now it gets a bit complicated... The reason is a combination of two things: (1) When the MediaLibrary reads from the database it also uses a MP2-Transaction. For this transaction of course a "BEGIN" statement is issued to the database ("BEGIN" is the short SQL syntax for "BEGIN TRANSACTION" in SQLite). However, when we just read from the database, we of course never call Commit() or Rollback(). Now why should we care? This results from the second cause (2): System.Data.SQLite pretends that it can deal with nested transactions. But in reality, this is not really the case. What actually happens is the following: When you begin a SQLiteTransaction on a fresh SQLiteConnection, a "BEGIN" statement is issued to the database and the so called "TransactionLevel" of the SQLiteConnection is raised from 0 to 1. If you now start a second SQLiteTransaction on the same SQLiteConnection, it does not issue a further "BEGIN" command. It just raises the TransactionLevel from 1 to 2. When you now Commit() the second SQLiteTransaction you would expect that the transaction is actually committed. But this is not the case. The only thing that happens is that the TransactionLevel of the connection is lowered to 1 again. Only when you Commit() the first SQLiteTransaction and the TransactionLevel is lowered to 0 again, it actually issued a "COMMIT" statement to the database.Now let's bring these two things together: We have a MP2-Transaction (containing a SQLiteTransaction) which is used for reading. That means we acutally issue a "BEGIN" to the database. The MP2-Transaction is disposed, but for the contained SQLiteTransaction Dispose() is not called - i.e. the TransactionLevel of the respective SQLiteConnection is still 1. Now the connection is returned to our ConnectionPool. MediaLibrary wants to write something to the database and gets a MP2-Transaction with the same underlying SQLiteConnection from our pool. We call SQLiteConnection.BeginTransaction() - and what happens? The TransactionLevel of this Connection was still 1. Therefore no "BEGIN" is issued to the database, but only the TransactionLevel of the SQLiteConnection is raised to 2. Later on the MediaLibrary wants to commit everything it has written. What happens: We do not issue a "COMMIT" to the database, we just lower the TransactionLevel from 2 to 1.How can this lead to a performance increase? SQLite can insert multiple thousands of rows into the database in one second when all inserts happen within a single transaction. But it can only handle a few transactions per second. What we achieved here is that we put many inserts into one transaction although we thought they were executed in several transactions. This gave us a performance improvement.Is that bug also in v0.07: Yes, but it doesn't matter... In v0.07 we close the respective SQLiteConection after the MP2-Transaction is disposed. Closing the SQLiteConnection also means resetting the TransactionLevel so the story above cannot happen...Now I thought why not use this "bug" to improve the speed - and I did some tests. In the end, however, the best thing is to properly call Dispose() on every SQLiteTransaction no matter what it was used for. Test result will follow anyway for documentation purposes...</blockquote>

Verification