How many simultaneous transactions can be performed on the same tables?

Question

MySQL

Hello.

I have a question about transaction performance.

Let's say there is some script that makes a selection from 6 tables, and then inserts into 4 other tables.
The entire script (including SELECT queries) is wrapped in a transaction.
This script is being run by 500 threads in parallel.

START TRANSACTION;
SELECT f FROM t1;
SELECT f FROM t2;
SELECT f FROM t3;
SELECT f FROM t4;
SELECT f FROM t5;
SELECT f FROM t6;
INSERT INTO t7;
INSERT INTO t8;
INSERT INTO t9;
INSERT INTO t10;
COMMIT;

I believe that in this way data consistency is 100% guaranteed.

Questions:
1. Is it permissible to wrap queries in SELECT transactions?
2. Is there any limit on the number of requests in a transaction?
3. Is there any limit on the number of threads that try to execute such a set of SQL statements at the same time?
4. How many concurrent transactions affect performance?

Thanks for answers.

Answer 1

1. Is it permissible to wrap queries in SELECT transactions?
- In the Read Committed mode, the SELECT query will not see the uncommitted data of your inserts, and if you want to see this data before the commit, then you need to add the select to the transaction.
2. Is there any limit on the number of requests in a transaction?
- It depends on the size of the log file for the transaction, the global parameter innodb_log_file_size
is 3. Is there any limit on the number of threads that try to execute such a set of SQL statements at the same time?
- There is a limit on the number of open transactions 128 * 1023.
4. How many concurrent transactions affect performance?
- The more you work with the database, the greater the load on it. )))