Do large transactions have linear or exponentially increasing costs for productivity / resource?

Question

I have a very general question:

1.) Given that I have a transaction (in mysql) that involves changing ten hundred thousand or even millions of records. Are these transactions linearly expensive compared to a very "small" transaction? Or will large transactions consume exponentially more resources than a small transaction? (in other words, to be avoided).

2.) Is it true that with such a large transaction, the load on mysql will go to a very high value until the transaction is fully processed?

The question is mainly related to MySQL, but if there is a server that is "much better" than it would be interesting to hear about them. And yes, the server / system has quite a lot of load from other processes (so this is not the only transaction processed in the system = medium / high conflict). Isolation levels are the levels of "higher / higher" isolation levels (which bring all the benefits of real transactions).

Many thanks

Answer 1

This is not a complete answer, I hope someone gives you useful tips on this.

But I can give you at least one piece of advice.

Of course, with the serializable isolation level, the biggest problem you will encounter is when you have a long transaction, you have a lot of chances that it will automatically roll back by the engine. At the serialized isolation level, if any other transaction commits something that modifies one of your deleted data, your transaction is disconnected. In the isolation level of serialization, you should think of your transaction as something that you may have to redo several times until this is normal. So ... if it is big and you are not alone, it can become very long or perhaps impossible.

If we are talking about changing all the rows of a table from millions of rows in a serializable transaction, you will certainly have to put an application level lock or semaphore, something more than a database transaction, to tell another process that you are doing a difficult task and that they should wait a bit and let you complete this important task :-)

But if you can consider this process a maintenance task on each line and that this is not a problem for your environment, that some of the lines are in a new state and some others are not yet ... then complete the transaction for each and not large. You should only make large serializable transactions if it is really important that all affected lines should switch their status at the same time (this is Atomicity). Most likely, this is not so, is it?