We have a large table of about 100 million records and 100+ fields and there are frequent select and update queries running related to this table. Now we have a requirement to set almost 50+ fields to null and we are planning to do this updation based on the primary key. We are aware that there will be a locking mechanism when two updates are trying to update the same record.
Our question is, what happens when an update and select query is trying to access the same record. For example in our case
case1: If we are selecting some 10000 records in one thread and during this select query execution if we are trying to update one of this 10000 records to null in another thread, Will both of this query executes without waiting for the other query? how will be the locking mechanism behave in this scenario?
case2: If we are updating 10000 records to null and during this update query execution if we are trying to select one of these 10000 records, Will both of these queries execute without waiting for the other query? how will be the locking mechanism behave in this scenario?
We are using MySQL 5.7, InnoDB engine and consider all parameters in MySQL is default
Apologizing for this basic question
Given your premise that you use InnoDB as the storage engine and default configuration options:
Readers do not block writers, and vice-versa, full stop.
A SELECT query (unless it's a locking read) can read rows even if they are locked by a concurrent transaction. The result of the query will be based on the latest version of the rows that were committed at the time the SELECT query's transaction snapshot started.
Likewise, an UPDATE can lock a row even if it is being read by a concurrent transaction.