When is it necessary to register transactions in the database?

Question

Database

Interested in what cases transactions in the database are required? If possible, examples please.

Answer 1

A transaction is an opportunity to combine several of your requests into one, within which it is possible to roll back the state of the database to its beginning, and only within the limits of the data being written.
They only make sense if you need data integrity control and if it exists in principle. To be more precise, if your application is to remain functional whenever the connection between the database and your application is broken, of course this only makes sense if there are data changes.
Or only if you have multi-user access to the data, to be exact. you have simultaneous access to the database from different sessions. Those. you can sacrifice the integrity of the database structure in case of failure (for example, take this possibility into account when reading data) by significantly speeding up its work if you have only one user and one connection to the database at any given time.
There is still a moment if you changed the database within the transaction and make a request including this data before its completion - then if such a request is made outside the transaction (for example, a neighboring session), then it will not see your changed data, but inside the transaction it will see.
It does not always make sense to block transactions (they are expensive in terms of database resources), sometimes locks are enough, i.e. before writing, you block access to the entire table (this can be done in a non-transactional database) or even to individual fields, and other sessions that try to read / change them will be suspended until unlocked.
The data connectivity itself, of course, implies that integrity control is needed, but it is not necessary, you can put in the code the possibility of incomplete filling in the data about the object and follow the order in which this data changes. For example, you first add an entry to the 'parents' table, with the number of children 0, then add an entry to the 'children' table about their children, i.e. instead of one transaction, you have two independent transactions, at any moment the structure is correct, but between the records the information about the children of the parents is incorrect (there are parents and no children), this is a very short moment and it is definitely not relevant if you have only one user while he changes the information, no one else will read this invalid data.

Answer 2

Always use transactions

Answer 3

1. If there is an insertion into 2 or more tables at the same time.
2. If there is a sample and then an insertion depending on conditions.
3. For any operations, it is more difficult than one select, one insert or one update.