How important is Begin Transaction and Commit?

Still very new to Xojo, coming from Filemaker. I have created a Xojo web app now that accesses a SQLite database. It successfully inserts, updates, etc. No problem there. But I want to place it in a cubeSQL folder over at 1701 Software to allow more than one user to access and write to that file simultaneously. When I asked Marco (the cubeSQL developer) about this, he said to make sure each transaction is committed by the user for record locking to work automatically for multiple users.

Does this mean I really have to explicitly begin each attempt to alter the database with DB.SQLExecute(“BEGIN TRANSACTION”) and then follow that attempt with DB.Commit?

I’m not sure how CubeSQL would be different, but typically you want to wrap any series of changes into a transaction where a single failure should, in effect, undo all the previous changes. For example, if you are manually keeping a count of the related records in table B in a field in the master record of table A, you would want a transaction when adding records to table B and updating the table A.Count so they stay in sync. (This is one, probably lame example, just to illustrate the point.)

Where modifications can be made individually without affecting data integrity, you typically don’t need a transaction.

If you don’t want two users to modify the same record simultaneously, you will need to come up with some locking mechanism, unless CubeSQL gives you one. Some databases have tools you can use for this purpose, or you can roll your own. FileMaker handles this for you very nicely.

There is nothing similar to transactions in FileMaker Pro, unfortunately.

Thanks, Kem. In this case, all modifications will be made individually without affecting data integrity, so it sounds like I’m okay without using the transaction model. I also just read that “by default, SQLite does a Commit after each SQL command that changes the database or its data.” (see http://documentation.xojo.com/index.php/SQLiteDatabase). So I guess what you’re saying is that if I started a series of database altering events “BEGIN TRANSACTION”, I can prevent the automatic commits of SQLite, and do the commit intentionally at the end of the series.

In terms of Transactions in Filemaker, there really is a way to do that using portals (see FileMaker Transactions - Transactional Scripting - Scale FileMaker)

Ah, I’d call that a workaround, but whatever, that certainly would work, and it’s not something I had considered. Thanks.

BTW, when posting a link in the future, just paste it. It will show up as a link without your using the “Link” tool in the toolbar. If you can fix the one above, please do so others don’t run into the problem of being taken to example.com. :slight_smile:

Didn’t notice wha the LINK code did in posts here. I changed it.

Here’s the doc page on Database Transactions.

Thanks, Paul. Very clear article. Helps a lot.