mysql 多个事务更新同一条记录产生死锁
mysql
以下是用show innodb status 查看的死锁信息，都是通过主键索引userId去更新记录，没有其他索引的影响，不应该会产生死锁啊，请大神帮忙分析下原因。
表索引如下
(....
PRIMARY KEY (userId),
UNIQUE KEY userId_UNIQUE (userId),
UNIQUE KEY userName_UNIQUE USING BTREE (userName)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
LATEST DETECTED DEADLOCK
140626 11:12:09
*** (1) TRANSACTION:
TRANSACTION 0 168550, ACTIVE 13 sec, process no 21006, OS thread id 139721036994304 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 2
MySQL thread id 168698, query id 1710850 localhost 127.0.0.1 root Updating
update users set imei='A00000455A4CFE',last_address='上海市闸北区秣陵路303号',lat=31.254289,lon=121.46208,last_login='2014-06-26 11:11:56',userStatus=11,deviceId='2B54F8EDD4A2DC4150BC5D8A4E0FB340',platform='android',updated='2014-06-26 11:11:56' where userId=15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168550 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;
*** (2) TRANSACTION:
TRANSACTION 0 168551, ACTIVE 1 sec, process no 21006, OS thread id 139720929810176 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 2
MySQL thread id 168699, query id 1710851 localhost 127.0.0.1 root Updating
update users set imei='A00000455A4CFE',last_address='上海市闸北区秣陵路303号',lat=31.254289,lon=121.46208,last_login='2014-06-26 11:12:08',userStatus=11,deviceId='2B54F8EDD4A2DC4150BC5D8A4E0FB340',platform='android',updated='2014-06-26 11:12:08' where userId=15
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168551 lock mode S locks rec but not gap
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 382 n bits 120 index PRIMARY of table microbiz_new.users trx id 0 168551 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000029264; asc d;; 2: len 7; hex 000000002d2a40; asc -*@;; 3: len 10; hex 7a686f757a696a69616e; asc zhouzijian;; 4: len 9; hex e591a8e5ad90e5bbba; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88;...(truncated); 8: len 4; hex 80000004; asc ;; 9: len 11; hex 3138363538313533393030; asc 18658153900;; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE;; 11: SQL NULL; 12: len 19; hex 7a7a686f7540706c696e6b636861742e636f6d; asc zzhou@plinkchat.com;; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc ;; 14: len 30; hex 687474703a2f2f3131352e32382e3136302e37313a383038302f6d696372; asc http://115.28.160.71:8080/micr;...(truncated); 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: SQL NULL; 21: SQL NULL; 22: len 1; hex 5a; asc Z;; 23: len 30; hex e4b88ae6b5b7e5b882e997b8e58c97e58cbae7a7a3e999b5e8b7af333033; asc 303;...(truncated); 24: len 8; hex 88bb7a1519413f40; asc z A?@;; 25: len 8; hex af08feb7925d5e40; asc ]^@;; 26: len 8; hex 800012515ab079e1; asc QZ y ;; 27: len 8; hex 800012515add6aa0; asc QZ j ;; 28: len 8; hex 800012515add6aa0; asc QZ j ;; 29: len 4; hex 80000001; asc ;; 30: len 4; hex 8000000b; asc ;; 31: len 4; hex 80000001; asc ;; 32: len 4; hex 80000003; asc ;; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3;...(truncated); 34: len 7; hex 616e64726f6964; asc android;;
*** WE ROLL BACK TRANSACTION (2)
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1377
52
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
How to create navicat premium
Apr 09, 2025 am 07:09 AM
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
Can I retrieve the database password in Navicat?
Apr 08, 2025 pm 09:51 PM
Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
MySQL and SQL: Essential Skills for Developers
Apr 10, 2025 am 09:30 AM
MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.
How to view database password in Navicat for MariaDB?
Apr 08, 2025 pm 09:18 PM
Navicat for MariaDB cannot view the database password directly because the password is stored in encrypted form. To ensure the database security, there are three ways to reset your password: reset your password through Navicat and set a complex password. View the configuration file (not recommended, high risk). Use system command line tools (not recommended, you need to be proficient in command line tools).
How to create a new connection to mysql in navicat
Apr 09, 2025 am 07:21 AM
You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.
How to execute sql in navicat
Apr 08, 2025 pm 11:42 PM
Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).
