Database Transactions
From Xojo Documentation
Contents
Database transactions were briefly covered in the Database Concepts section. But when do you actually need to use database transactions? The answer is "it depends".
A transaction is a collection of changes to the database. It can be a single change, such as an INSERT statement, or it can be many changes such as a combination of many INSERT and UPDATE statements. When changes are made within a transaction, they are generally not permanent (and not visible to other connections looking at the database) until you commit them. In order to use transactions effectively, you have to understand how they work with the database engine you are using. Some databases start a transaction for you automatically. And some will also automatically commit for you after each change so you don't have to actually commit manually.
Some databases let you use a "read uncommitted" mode where data that has not been committed can be viewed by other connections. This is usually a special case use for specific performance reasons.
Data Integrity
Generally, you do not want to commit after every database change as this can cause your data to become inaccurate should an error occur with updates, as seen by the following Banking example.
Banking Example
Suppose you want to transfer $10 from a savings account to a checking account.
Here are the starting amounts:
- Savings: $100
- Checking: $50
If you remove $10 from savings and commit, you have a database that looks like this:
- Savings: $90
- Checking: $50
There is now $10 in limbo. The next step is to add the $10 to checking and commit resulting in this:
- Savings: $90
- Checking: $60
This is how every works when nothing goes wrong. But having that $10 in limbo is a big gamble. If your app or database crashes before it can do the last step, you end up with the $10 being lost and your database integrity compromised.
Transactions prevent this problem. Look at the example again.
Here are the starting amounts:
- Savings: $100
- Checking: $50
You start a transaction and you remove $10 from savings. This is the same as before except you did not commit the change here so the data is not final just yet.
- Savings: $90
- Checking: $50
Now you can add $10 to checking, again without committing to get this as before:
- Savings: $90
- Checking: $60
As the final step, you can now commit these changes. This tells the database to apply the changes in one single step (the transaction). Because you changed the values in a single transaction, the data was never permanently in the database in its intermediate form. If something bad happened before you were able to commit (or even when trying to commit) then the database transaction would be been cancelled (called a rollback) and the data would have remained in its original state with $100 in savings and $50 in checking.
This is how transactions give you database integrity.
Data Isolation
As mentioned above, by using a transaction you prevent the database from making your changes permanent until they have all completed successfully. This is important for data integrity. But if you are using a multiuser database it is also important for data isolation purposes.
When multiple users are connected to the database, you always want them to see the data in its current state. Data that is in the middle of a being changed is not really current until the changes are completed. So you do not want user A to see changes that user B is in the process of making.
By using transactions you prevent this problematic situation from occurring.
Performance
If you commit after every database change, you force your database to do a lot of work behind the scenes to make the data permanently available. This is not a big problem when dealing with small amounts of data, but it can really add up when dealing with lots of data.
For example, if you are importing thousands of rows of data into a specific table, committing after each row could cause your import to take several minutes because of all the overhead. Switching to a transaction that only commits at the end (or even just every 1000 rows) could result in a tremendous improvement. It's possible for times to drop from several minutes down to several seconds when using a transaction in this manner.
Transactions give you better performance.
Using Transactions
So now you know that transactions are often the way to go, how do you use them? Unfortunately, that varies depending on the database engine.
With SQLite (SQLiteDatabase), if you do not explicitly start a transaction, then your command is put in an implicit transaction which essentially means that each database change is made permanent for you automatically. As I've shown you above, this may not be what you want.
With SQLite, you use the "BEGIN TRANSACTION" SQL command to start a transaction. Other database engines have similar commands. When you are finished with the transaction and want to make the changes permanent, you send the "COMMIT" command. Or if you need to cancel the transaction, you send the "ROLLBACK" command.
Xojo code for the Banking Example might look like this:
// connected to a SQLite database and defined as:
// db As SQLiteDatabase
db.SQLExecute("BEGIN TRANSACTION;")
// Withdraw 10
db.SQLExecute("UPDATE Savings SET Type = 'Withdrawal', " + _
"Amount = -10 WHERE AccountNum = '123456';"
If db.Error Then
MsgBox(db.ErrorMessage)
db.Rollback
Return
End If
// Deposit 10
db.SQLExecute("UPDATE Checking SET Type = 'Deposit', " + _
"Amount = 10 WHERE AccountNum = '123456';")
If db.Error Then
MsgBox(db.ErrorMessage)
db.Rollback
Return
End If
db.Commit