An example of an MySql type conversion that causes a row lock to be upgraded to a table lock

Editor to share with you an example of MySql type conversion leading to the upgrade of row locks to table locks. I hope you will gain something after reading this article. Let's discuss it together.

In the write statement of MySql, when the assignment of a table column does not match the table type, the underlying optimizer of MySql plays a role and makes a forced type conversion, which can operate normally at this time, but will cause the row lock to be upgraded to a table lock. Examples are as follows

Take the student table as an example, the table field type:

The table is as follows:

Open two session session windows and change the autocommit mode of MySql in both session windows to manual commit

> set autocommit=false

Execute the update statement in session window 1, but do not commit the transaction. The age column specifies the int type when creating the table, and the string '100' is assigned in the update statement here. In the optimizer of MySql, the string' 100' is automatically converted to integer 100, and then SQL retrieval is performed.

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

Then update the other irrelevant data in session window 2.

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

Normally, two SQL statements operate on different rows of data and do not affect each other, but the update operation in session 1 blocks the update operation in session 2.

An update operation is performed in session 1, but no transaction commit is performed, and the isolation level of the transaction is Read Committed, so the updated result in session 1 cannot be seen in session 2. However, blocking occurs when an update operation to other rows of data is performed in answer 2. It can be seen that there is a strong turn in the assignment of the SQL statement in session 1, which causes session 1 to upgrade from a row lock to a table lock, locking the entire student table, thus blocking the SQL in session 2. Let's perform a transaction commit for the update operation in session 1, and the update operation in session 2 will continue.

After the commit manually commits the transaction for the update operation in session 1, session 1 releases the table lock on student, and the update operation in session 2 can continue.

Finally, the commit transaction commit is also performed for the update in session 2, and both SQL are updated. The contents of the student table are as follows:

Judging from the above case, when the SQL statement assignment does not match the table column type, the optimizer of MySql is forced to convert to the matching type, resulting in the row lock being upgraded to the table lock. Therefore, in the development, we must pay attention to the matching of types to avoid upgrading row locks to table locks, affecting concurrent performance.

After reading this article, I believe you have a certain understanding of "an example of upgrading a row lock to a table lock due to MySql type conversion". If you want to know more about it, please follow the industry information channel. Thank you for reading!