Do transactions affect database overhead?

Question

Will a service message be added to place DB transactions around each service method in our application?

Currently, we use only database transactions, where this is an explicit / obvious necessity. I recently proposed transactions in all service methods , but some other developers asked a reasonable question: will this add overhead?

I feel it is not - automatic commit is the same as a transaction from a DB perspective. But is this true?

DB: MySQL

Answer 1

You are right, with autocommit every statement is enclosed in a transaction. If your maintenance methods execute multiple sql statements, it would be nice to wrap them in a transaction. Take a look at this answer for more details, and here is a good blog post on this topic.

And in order to answer your question, yes, transactions do increase performance overhead, but in your particular case you won’t notice the difference, since you already have autorun enabled if you don’t have long operators in the service methods, which will increase the time locking tables involved in transactions. If you simply close several statements inside a transaction, you will receive one transaction (instead of a transaction for each individual statement), as indicated here ("A session with autoload enabled can execute a transaction with several statements, starting it with the explicit START TRANSACTION or BEGIN statement and ending with with the COMMIT or ROLLBACK operator "), and you will achieve atomicity at the service method level ...

In the end, I would go with your decision if it makes sense from the point of view of achieving atomicity at the level of the service method (which, I think, you want to achieve), but there are + and - effects on performance, depending on your requests, requests etc....

Answer 2

Yes, they can add overhead. The additional “accounting” necessary to isolate transactions from each other can become significant, especially if transactions remain open for a long time.

Answer 3

The short answer is that it depends on the type of table. If you use MyISAM, by default, there really are no transactions, so it should not affect performance.

But you should use them anyway . Without transactions, there is no demarcation of work. If you go to InnoDB or a real database such as PostgreSQL, you still want to add these transactions to your maintenance methods, so you can also make it a habit while you are worth nothing.

In addition, you should already be using a transaction store. How do you clean if the maintenance method does not currently work? If you write some information to a database, and then your service method throws an exception, how do you clear this incomplete or erroneous information? If you used transactions, you would not have to - the database would throw back the data for you. Or what do you do if I am halfway through a method and another request arrives and finds my semi-recorded data? Will it explode when he searches for the other half, which is not there yet? The transaction store will handle this for you: your transactions will be isolated from each other so that no one else can see the partially written transaction.

Like all databases, testing with realistic data and realistic workloads will be the only final answer. I recommend that you always do this, no matter what you suspect, because when it comes to databases, very different code codes are activated when the data is large or not. But I strongly suspect that the cost of using transactions even with InnoDB is low. In the end, these systems are constantly used constantly, every day, by large and small organizations that depend on successful transactions. MVCC adds very little overhead. The benefits are huge, the costs are low.