Database transactions when ?

Hi all,

Just a quick question regarding the use of database transactions.

Should they be used for every single transaction (Where SQLExecute is used) or are there only certain cases, such as modifying the data within a record ?

Thanks.

Database transactions should be used whenever data is written to the database. This includes SQLExecute where you’re doing Updates, Inserts, Deletes, or alter or creating/dropping tables/views via SQL. This also includes DatabaseRecord (Xojo class) inserts and Recordset (Xojo class) Updates and Deletes.

The idea is that if you have an error in one of these things you can rollback everything in the transaction as if you had never tried it in the first place.

The perfect example is having a parent-child relationship such as Invoice and Invoice Line Items. You start the transaction, save all the invoice data so you can get the InvoiceID and then save the Invoice Line Items data (using said InvoiceID for the relationship). If you have no errors (you’re checking for errors after every DB action, right?) you can simply commit at the end after all the inserts have happened. But if you have an error on Invoice Line 89 out of 100 then you can Rollback ALL of the inserts and it’s like none of it happened.

They should be used anytime all changes need to be saved or discarded together. If you are modifying a single record and that change can remain even if subsequent changes are aborted, you don’t need a transaction. If you are modifying a group of records with a single UPDATE or DELETE, you don’t need a transaction since the engine will create its own transaction behind the scenes.

For example, let’s say you want to print a bunch of records so you SELECT the ones where print_date is NULL. As you print each one, you set print_date to the current date, but in the middle of the process you have to abort. If this was all in a transaction, you’d have to start again, but outside a transaction, you could resume with the last unprinted record.

On the other hand, if you are adding line times and updating the total of a related invoice, you probably want that all in a transaction so the value doesn’t get out of sync. (Yes, that should be a trigger anyway, but play along.)

Thanks chaps.

Yes, I’m using error checking after every action, and tripping it up a few times to test it all works as expected.

So far I’m only ever writing, deleting or updating a single record at a time, and the data which is stuffed into each record has all been validated just prior to performing the database operations.

There will be some instances of updating multiple records at some point because I have some lookup tables which will have a customisable display order. I plan to do all the edits in an editable listbox, such as insert at position x and then move all others down by one. It shouldnt be too difficult since i have the PK for each record stored in the rowtag, and for certain other colums I have lookup key values stored in the relevant celltags.

Making liberal use of these different tag areas is a godsend. The plan is to tie those into the code that updates the respective records.