MySQL Update – How to Avoid Database Locking

lockingmariadbmigrationMySQLupdate

There is a update query I need to run on a table in production. The update would touch millions of rows and I would take a couple of minutes.

The database is Mysql.

I was wondering if the table would be locked during this update? The desired behaviour is that it should not be locked as I still want other operations to be possible while the update is going on.

Is there a way I can check before hand? What could I look for to find out the effect of such query? Is there something like a query plan from where I can see if the table would be locked or not?

Best Answer

Any UPDATE transaction will always acquire locks on the table being updated in some capacity. This is the normal default behavior in all modern relational database management systems. It's just a matter of the type of lock and how long a particular row stays locked during an UPDATE. Depending on the isolation level your database is configured for, even though a row may be locked, doesn't necessarily mean things like reads of that row will be blocked. You can read up more here on locks in MariaDB.

To your follow up question in the comments, depending on the isolation level, if an UPDATE is locking a set of rows that causes blocking on other subsequent queries running against those same rows, then those other queries will wait until those rows are no longer locked by the UPDATE query, to execute, in general.

Related Question