sqlitetcl

Where/when to begin a transaction when mutliple procedures generate data to update/insert records in db for a single request?


My question concerns using Tcl as a local server for a desktop application, and SQLite as the storage.

As the user types in a UI container, composition requests are sent to Tcl. These may invoke around ten procedures, most of which will generate some data to either update or insert records in a small number of database tables. There is little data passed in each request and little generated to be written to the database; and the requests should come in rapidly just as sporadic as the user types (but not at each key stroke).

After a request arrives and is validated, should a database transaction be commenced, there, and then each procedure can write the data it generates to the database in the order invoked; and if that all returns back to the procedure which commenced it all, the transaction can be committed or rolled back? Or, should the data generated by the procedures (since it is small) be written to RAM (in a dictionary, perhaps) and, then, after all the procedures return, commence a transaction to write it all, sort of together, and then commit or rollback?

As long as the procedures are independent in terms of not writing to the same database rows, is there reason for one approach to be preferrable to the other?

Thank you for considering my question.


Solution

  • You usually write things immediately to the database in a server because you don't know that another request will be coming. Bunching up changes into a meaningful overall change is a job for the client, and one of the reasons we often make clients thicker than simple forms. (The client may use SQLite locally to itself to hold intermediate state, but that's unrelated to what the server part does.)

    Both reading from and writing to the DB require a transaction; it will start one for you if you don't start one yourself. The main reason for an explicit transaction is so you can group several statements into a single action, especially where that action involves writing to multiple tables or a write that is partially dependent on a read. Care is needed with transactions that start as read transactions and upgrade to writing; those can deadlock if done in parallel (but you can ask for them to start as upgraded which avoids the deadlock). Keeping transactions short and simple (when writing) avoids most trouble even in busy services.