AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
My sql deadlock found8/18/2023 ![]() Exclusive lock (X lock) : Allows a locked transaction to update or delete a row.Shared lock (S lock) : Allows a locking transaction to read a row.InnoDB implements standard row-level locks, including two types: shared locks (s-locks) and exclusive locks (X-locks). mysql insert into account values(null, 'Yan',100) īefore analyzing the deadlock log, let's introduce the lock Meanwhile, transaction A's insert changes from blocking to deadlock error. Select * from information_schema.innodb_locks Ĥ) Transaction B performs the insert operation and the insert succeeds. Table structure: //id is an autoincrement primary key, name is a non-unique index, CREATE TABLE 'account' (' id 'int(11) NOT NULL AUTO_INCREMENT,' name 'vARCHar (255) DEFAULT NULL, `balance` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 Copy the codeĮnable two terminals to simulate transaction concurrency, and the execution sequence and experimental phenomena are as follows:ġ) Transaction A performs the update operation and the update succeeds mysql update account set balance =1000 where name = 'Wei' Query OK, 1 row affected (0.01sec) Copy the codeĢ) Transaction B performs the update operation and the update succeeds mysql update account set balance =1000 where name = 'Eason' Query OK, 1 row affected (0.01sec) Copy the codeģ) Transaction A is blocked by the insert operation mysql insert into account values(null, 'Jay', 100) Copy the code Gemma Holt Category:ĭeadlock occurs, how to troubleshoot and solve it? This article will explore that question with youĭatabase isolation level: mysql select |Īutomatic submission off: mysql set autocommit=0 ![]()
0 Comments
Read More
Leave a Reply. |