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)
Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AMInnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.
MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AMCompared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AMMySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.
MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AMMySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.
MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AMMySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.
MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AMMySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.
The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AMSQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.
MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AMThe basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
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
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
WebStorm Mac version
Useful JavaScript development tools
ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment
Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
