What is the effect of disabling automatic fixation of a database connection?

Question

If I establish a connection with manual commit, does this mean that my process will lock the database? If I need to execute several SQL queries, which probably will work 4-5 hours, does this mean that at that time no other user will get access to my database?

I am using a web application script where thousands of users access the same database.

connection.setAutoCommit(false); //multiple sql query that will probably take 4-5 hours to be executed connection.commit(); 

Answer 1

Setting autocommit to false usually does not affect reading, so if the queries you run should not be a problem.

What autocommit = false guarantees are that you are fulfilling your requests (inserts, deletions, updates) in a transaction, which means that they either all succeed (with a commit at the end), or fail, and roll back.

When you insert, update or execute select ... for update , some rows will be locked, and this is harder to predict, since it depends on your engine, version of Mysql, isolation level, etc.

If, for example, autocommit = false, and two users need to update the same lines at the same time, then one will be blocked waiting for the completion of the first, either with commit or rollback.

Let's say USER1 starts updating your database, which will focus on 10 rows.

You start a transaction, perform an update, make a couple more requests ...

Before committing / rolling back USER2 , the same update or update is launched, which will focus on one or more lines that are updated by USER1 .

USER2 will be locked, waiting for USER1 to commit or roll back before an update can be performed.

One way to test is to open two different database connections, for example, through the command line or some other client, and simulate this behavior. Set the auto-exchange value to false, perform the update on one client, do the same in the second and see that it hangs there until you complete or roll back the first client.

This method can be very convenient to help you understand what is going on behind the scenes.

Answer 2

connection.setAutoCommit (false); means that you are starting a transaction on this connection. All changes that you will make with the database tables in this connection will be saved when committing or returning when rolling back (or disconnecting without committing). This does not mean that you are blocking the entire database. Whether other users will be blocked while trying to access the tables you use for transactions will depend on the operations performed by your transaction and the level of transaction isolation.

Answer 3

It depends on the level of isolation and what you do:

If you read:

• Dirty Read / Read Commited: Other tasks will not be affected.
• Repeatable reading: Other reading tasks will not be affected. Other recording problems may be affected. (Depends on implementation)
• Serialization: other reading tasks will not be affected, records will be.

If you perform updates, other tasks will be affected. Generally speaking, you should not make updates in long transactions.