MySql type conversion causes row lock to be upgraded to table lock

In the write statement of MySql, when the value assigned to the table column does not match the table type, the underlying optimizer of MySql will take effect and perform a forced type conversion. At this time, the operation can be normal, but the row lock will be upgraded to the table Lock. The example is as follows

Take the student table as an example, the table field type:
The table content is as follows:

Open two session windows, And change the automatic submission mode of MySql in the two session windows to manual submission

>set autocommit=false;

Execute the update statement in session window 1, but do not commit the transaction. The age column is specified as int type when creating the table. The string '100' is used for assignment in the update statement. The MySql optimizer will automatically convert the string '100' into an integer 100, and then perform the SQL retrieval. .

>update student set class=3 where age='100'

Then perform update operations on other irrelevant data in session window 2

>update student set age=28 where name='lzj';

Under normal circumstances, the row data operated by the two SQL statements are different and will not affect each other when executed. However, The actual update operation in session 1 blocked the update operation in session 2
The update operation was performed in session 1, but the transaction submission was not executed. The isolation level of the transaction was Read Committed, so it is still seen in session 2. Less than the updated results in session 1. However, when performing data update operations on other rows in Session 2, blocking occurred. It can be seen that the assignment of the SQL statement in session 1 was forced, causing the row lock in session 1 to be upgraded to a table lock, locking the entire student table, and thus the SQL in session 2 was blocked. Next, perform a transaction commit on the update operation in session 1, then the update operation in session 2 will continue to be executed.
Execute the update operation in session 1commitAfter manually submitting the transaction , session 1 releases the student's table lock, and the update operation in session 2 can continue to be executed.
Finally, commit transaction submission is also executed for the update in session 2. Both SQLs are updated. The content of the student table is as follows:

From the above case view It is known that when the SQL statement assignment does not match the table column type, MySql's optimizer is forced to convert it to a matching type, causing the row lock to be upgraded to a table lock. Therefore, you must pay attention to type matching during development to avoid row locks being upgraded to table locks, which will affect concurrency performance.

Related recommendations:

Detailed introduction to MySQL row-level locks, table-level locks, and page-level locks

MySQL lock usage table-level lock

The above is the detailed content of MySql type conversion causes row lock to be upgraded to table lock. For more information, please follow other related articles on the PHP Chinese website!