MariaDB. Use Transaction Rollback without locking tables

Question

On a website, when a user posts a comment I do several queries, Inserts and Updates. (On MariaDB 10.1.29)

I use START TRANSACTION so if any query fails at any given point I can easily do a rollback and delete all changes. Now I noticed that this locks the tables when I do an INSERT from an other INSERT, and I'm not talking while the query is running, that’s obvious, but until the transaction is not closed.

Then DELETE is only locked if they share a common index key (comments for the same page), but luckily UPDATE is no locked.

Can I do any Transaction that does not lock the table from new inserts (while the transaction is ongoing, not the actual query), or any other method that lets me conveniently "undo" any query done after some point?

PD:

I start Transaction with PHPs function mysqli_begin_transaction() without any of the flags, and then mysqli_commit().

Are you using MySQL or MariaDB? What version? Please edit your question and show us the DDL of the table(s) in question and the code in your transaction. — Dave
This answer may be helpful. Since you know what row(s) you are going to be updating perhaps a select with update for that row would only lock that row instead of the table. — Dave
@Paul ‘comment’ table does not accept any INSERT, until the transaction is closed (if there is an INSERT on the transaction). I showcased the comments table since its simpler, but the same happens when publishing Posts, where I do about 8 queries (multiple INSERTS) and I also move files on the server, so until all is not completed correctly I do not commit, in that case locking time is higher, it’s not much maybe 1 second, but if I wait 40 second to COMMIT, that’s 40 seconds locked. — Vixxs

Paul Spiegel Paul Spiegel · Accepted Answer · 2019-06-02T15:39:00

I don't think that a simple INSERT would block other inserts for longer than the insert time. AUTO_INC locks are not held for the full transaction time.

But if two transactions try to UPDATE the same row like in the following statement (two replies to the same comment)

UPDATE comment SET replies=replies+1 WHERE com_id = ?

the second one will have to wait until the first one is committed. You need that lock to keep the count (replies) consistent.

I think all you can do is to keep the transaction time as short as possible. For example you can prepare all statements before you start the transaction. But that is a matter of milliseconds. If you transfer files and it can take 40 seconds, then you shouldn't do that while the database transaction is open. Transfer the files before you start the transaction and save them with a name that indicates that the operation is not complete. You can also save them in a different folder but on the same partition. Then when you run the transaction, you just need to rename the files, which should not take much time. From time to time you can clean-up and remove unrenamed files.

MariaDB. Use Transaction Rollback without locking tables

2 Answers