mysql Transactions

[AdamSEY]

Hi, I have a simple question regarding transactions, Does beginTransaction just set auto_commit off and then commits all the queries? Does it also locks the table or we've to lock the table by querying
SELECT ID FROM myTable FOR UPDATE ...

[ocram]

Good question again, thank you!

Calling beginTransaction does indeed turn autocommit off, so that the subsequent statements will not be executed immediately but will be executed in one atomic batch as soon as you finish or commit the transaction, which also turns autocommit on again.

That’s just the way transactions work. So I think everything is fine here in regard to how the library works or how you use it or intend to use it.

Assuming you’re using MySQL, there are some quirks, though:

• Data definition language (DDL) statements, such as CREATE TABLE, ALTER TABLE, TRUNCATE TABLE, DROP TABLE, CREATE INDEX or DROP INDEX, implicitly end any transaction and thus break the atomicity and the ability to roll back [1]. So you really shouldn’t use those in transactions.
• Both LOCK TABLES and UNLOCK TABLES implicitly commit any active transaction, too. And also the other way round, beginning a transaction implicitly releases existing table locks [2]. So table locking does indeed not play well with transactions in MySQL. The two are practically incompatible.

Your SELECT ... FOR UPDATE, on the other hand, should work perfectly well with transactions [3]. You may need this if you execute queries using SELECT within your transactions.

Finally, if you only use data manipulation language (DML) statements in your transactions, such as INSERT, UPDATE or DELETE, you should be fine with pure transactions, without any additional locking.

Does that help?

[AdamSEY]

Thank you so much for your helpful answer, referring to this question in readme.md file would be helpful for others.

Thanks again for such a great answer.