I’m wondering what people think about using database transactions. In a multi-user or multi-threaded application, or a web app, I think bracketing database activity (other than maybe SELECT statements) in BeginTransaction and CommitTransaction (or RollbackTransaction) is absolutely necessary. But what about single-user desktop-type apps where the likelihood of simultaneous database activity is minimal.
I tend to go the route of explicitly using transactions but am wondering if it is really needed in apps with a local database like SQLite.
In ActiveRecord we ALWAYS do a transaction even in single user cases whenever we are inserting, updating, or deleting. For us it costs nothing extra and we’ve had instances where a single user application becomes multi-user and we literally have to do nothing. Plus, we have events that fire inside the transaction so that the user can do work inside the transaction (like updating related records or deleting related records).
For any parent-child data structure (think invoice and InvoiceLineItems) a transaction is the ONLY way to be truly safe. If you don’t have a transaction and you save the Invoice (to get the InvoiceID) and then get an error on the insertion of InvoiceLineItem 99 out of 100 the transaction is the only safe way to back it all out.
Bob, is this really dictated by single or multi-user or the actual operation on the DB?
For single actions such as an update or insert of single record/table there really is nothing to rollback if the operation fails. Checking the error level after the operation indicates success or failure.
I understand when you are doing multiple actions or the operation touches more than a single record/table, such as invoice and invoice detail, where one operation may be successful and the second fails, then using transactions is a must.
I am not seeing where this is dictated by the type of application, single or multi-user.
This kind of stuff can (should actually) be handled by triggers, not the client. Pushing that logic into the database itself is another way of wrapping it into a transaction implicitly, the client doesn’t have to start transactions with that setup at all, because the Insert/update call will always fail entirely (not partially) if it should fail for some reason. I don’t think I have ever started a transaction in my Xojo code. The problem with starting transactions from the client is that the client might die midway, leaving dangling transactions open on the server.
It’s not dictated by single or multi-user, really. Because of the way we implement events (before/after Create, Before/After Update, Before/After Save, Before/After Delete) in ActiveRecord we really had no choice but to do the transaction. But we create the transaction regardless of whether or not you’re using the events.
We give the ability to create your own transaction so if you’re inserting/updating/deleting a bunch of records you can get the speed savings of being inside a transaction.
Sure there is. If an update fails, the Rollback will revert the affected record(s) back to their original column values and if there is a failure during an operation that includes inserts, the Rollback will remove any new entries. Sorry, I got mixed up in which post I was reading and replying to. I see you already know this.
Database transactions are not strictly a requirement for most systems. It’s just good practice.
At my day job, where we work on some pretty huge data warehouses (SQL Server) in a high demand environment, it’s not uncommon to deliberately not use Transactions to reduce latency as much as possible for performance reasons. There is some risk of data integrity issues for sure, but depending on the type of operation the performance gains can outweigh the risk. I only say that as a matter of observation - not as a general recommendation.
Edit: Included SQL Server above and removed earlier comment.
So what I’m gleaning from the responses so far is that, again in a single user app, transactions aren’t really necessary if the operation is a single hit on the database. In cases where there are subsequent or dependent operations transactions are needed.
For example (unchecked coding here)
UPDATE TABLE Quantum SET Orbit = 'Round" WHERE ElectronsPresent = True
does not need to be embedded, whereas something like
CREATE TABLE Atomic (pk as Integer Primary Key, Nucleus Varchar, ElectronsPresent Boolean, Orbit Varchar)
INSERT INTO Atomic (Nucleus, ElectronsPresent, Orbit) VALUES ('Oxygen', True, 'Elliptical')
INSERT INTO Atomic (Nucleus, ElectronsPresent, Orbit) VALUES ('Xenon', True, 'Erratic')
should be within a transaction.
Transactions arent really dependent on single user vs multi-user usage
They are logical groupings of data where everything has to either all get inserted or not to keep from having data integrity issues
That said SOME DB engines will not let you put DDL in a transaction
So the create table statement MAY execute regardless of whether it is in a transaction or not and may NOT be something you can roll back
The docs for whatever db you’re using should say whether this is the case or not
For myself, I worry about how complex a single-user application can get, especially when you might have overlapping events on top of normal user activity coming from timers and/or threads.
As I said earlier, Transactions are “good practice”. The point of good practices is to (hopefully) reduce headaches down the road.
Plus there’s this little tidbit from SQLiteDatabase under Transaction, “To ensure data integrity and to improve performance, you should create a transaction and do your database changes in the transaction.”
Each kind of database, e.g., SQLite, SQL Server, PostgreSQL, etc. will have it’s pros and cons. I’ve spent 20 years in SQL Server, but SQLite is still relatively new to me.
My bad. Now I can’t edit it. But here’s where it should go SQLiteDatabase.
Maybe it should be a Forum feature, that if you add a link, but don’t change the default url=http://example.com address, it gives you a warning when you try to save the post? Oh well.
Not single database. Single table, where the update is not part of any group of updates that should work or fail together. But even at that, if you have a loop doing lots of updates to a table and even if not a problem if some work and some fail, a transaction can give you MUCH better performance.
So as a general rules, I would suggest always using a transaction when modifying a database. Read only operations like a SELECT need not be in a transaction.
You usually dont need to put a read in a transaction BUT there are places & reasons you might
One of those hacky things that everyone just takes for granted now is one
Used to be one of the few reliable ways to always get a unique ID for a row identifier if you put the update & read in the same transaction
Learned that one from Sybase/M SQL nearly 30 years ago when they did not support sequences
And it still works quite reliably to this day
