行级锁瓶颈

最新推荐文章于 2024-07-26 16:04:09 发布
最新推荐文章于 2024-07-26 16:04:09 发布
阅读量1.9k 收藏
点赞数
分类专栏： 性能杂谈
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lanbingkafei/article/details/8859834
版权

第一次在性能测试中遇到行级锁，记录如下：


一、判断方法：

mysql> show status like '%lock%';
+------------------------------------------+-----------+
| Variable_name                            | Value     |
+------------------------------------------+-----------+
| Com_lock_tables                          | 0         |
| Com_unlock_tables                        | 0         |
| Innodb_row_lock_current_waits            | 8         |
| Innodb_row_lock_time                     | 124805490 |
| Innodb_row_lock_time_avg                 | 4580      |
| Innodb_row_lock_time_max                 | 51564     |
| Innodb_row_lock_waits                    | 27245     |
| Key_blocks_not_flushed                   | 0         |
| Key_blocks_unused                        | 73575     |
| Key_blocks_used                          | 33596     |
| Performance_schema_locker_lost           | 0         |
| Performance_schema_rwlock_classes_lost   | 0         |
| Performance_schema_rwlock_instances_lost | 0         |
| Qcache_free_blocks                       | 0         |
| Qcache_total_blocks                      | 0         |
| Table_locks_immediate                    | 3985922   |
| Table_locks_waited                       | 0         |
+------------------------------------------+-----------+
17 rows in set (0.00 sec)



二、揪出被锁的SQL

mysql> show full processlist;
+--------+-----------+----------------------+--------------------------+---------+-------+--------+---------------------------------------------------------------------------------------------------------------------+
| Id     | User      | Host                 | db                       | Command | Time  | State  | Info                                                                                                                |
+--------+-----------+----------------------+--------------------------+---------+-------+--------+---------------------------------------------------------------------------------------------------------------------+
| 172022 | writeuser | 192.168.96.28:7143   | NULL                     | Sleep   | 23092 |        | NULL                                                                                                                |
| 172023 | writeuser | 192.168.96.28:7144   | wishdb_1                 | Sleep   | 23033 |        | NULL                                                                                                                |
| 172041 | writeuser | 192.168.96.28:7152   | wishdb_2                 | Sleep   | 22534 |        | NULL                                                                                                                |
| 172105 | writeuser | 192.168.96.28:7183   | wishdb_2                 | Sleep   | 22534 |        | NULL                                                                                                                |
| 173501 | writeuser | 10.255.254.210:14360 | wishdb_2                 | Sleep   |    13 |        | NULL                                                                                                                |
| 173502 | writeuser | 10.255.254.210:14367 | wishdb_1                 | Sleep   |    11 |        | NULL                                                                                                                |
| 173503 | writeuser | 10.255.254.210:14371 | orders_basic             | Sleep   |    12 |        | NULL                                                                                                                |
| 173504 | writeuser | 10.255.254.210:14395 | test                     | Sleep   |    10 |        | NULL                                                                                                                |
| 173505 | writeuser | 10.255.254.210:14399 | merchant_product         | Sleep   |    10 |        | NULL                                                                                                                |
| 173506 | writeuser | 10.255.254.210:14401 | ddpim_00                 | Sleep   |    10 |        | NULL                                                                                                                |
| 173507 | writeuser | 10.255.254.210:14403 | ProductConfigDB          | Sleep   |    10 |        | NULL                                                                                                                |
| 173509 | writeuser | 10.255.254.210:14412 | CKConfigDB               | Sleep   |     9 |        | NULL                                                                                                                |
| 173510 | writeuser | 10.255.254.210:14413 | VirtualCommoditySystemDB | Sleep   |     9 |        | NULL                                                                                                                |
| 173511 | writeuser | 10.255.254.210:14418 | VirtualOrderDB           | Sleep   |     8 |        | NULL                                                                                                                |
| 173513 | writeuser | 10.255.254.210:14429 | ConfigInterDB            | Sleep   |    14 |        | NULL                                                                                                                |
| 175324 | writeuser | 10.255.254.49:48867  | wishdb_2                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175325 | writeuser | 10.255.254.49:48868  | wishdb_1                 | Query   |    28 | update | insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('5t75','46088',1,now(),now())  |
| 175326 | writeuser | 10.255.254.49:48875  | wishdb_1                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175327 | writeuser | 10.255.254.49:48876  | wishdb_1                 | Query   |    27 | update | insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('by69','50213',1,now(),now())  |
| 175328 | writeuser | 10.255.254.49:48879  | wishdb_1                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175329 | writeuser | 10.255.254.49:48881  | wishdb_2                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175330 | writeuser | 10.255.254.49:48882  | wishdb_1                 | Sleep   |    79 |        | NULL                                                                                                                |
| 175331 | writeuser | 10.255.254.49:48883  | wishdb_2                 | Query   |    27 | update | insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('df59','49397',1,now(),now())  |
| 175332 | writeuser | 10.255.254.49:48884  | wishdb_2                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175333 | writeuser | 10.255.254.49:48886  | wishdb_2                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175334 | writeuser | 10.255.254.49:48885  | wishdb_1                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175335 | writeuser | 10.255.254.49:48887  | wishdb_2                 | Sleep   |   131 |        | NULL                                                                                                                |
| 175336 | writeuser | 10.255.254.49:48888  | wishdb_2                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175337 | writeuser | 10.255.254.49:48889  | wishdb_1                 | Sleep   |   486 |        | NULL                                                                                                                |
| 175338 | writeuser | 10.255.254.49:48891  | wishdb_1                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175339 | writeuser | 10.255.254.49:48892  | wishdb_2                 | Sleep   |    27 |        | NULL                                                                                                                |
| 175340 | writeuser | 10.255.254.49:48893  | wishdb_1                 | Query   |    27 | update | insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('vb39','52329',1,now(),now())  |



或者更详细信息：

mysql> show engine innodb status \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
130427 15:49:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 34 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 9900 1_second, 9900 sleeps, 917 10_second, 846 background, 846 flush
srv_master_thread log flush and writes: 9926
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 39571, signal count 194161
Mutex spin waits 9467153, rounds 16028584, OS waits 21994
RW-shared spins 198972, rounds 718960, OS waits 8493
RW-excl spins 15185, rounds 505256, OS waits 7209
Spin rounds per wait: 1.69 mutex, 3.61 RW-shared, 33.27 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
130422 18:32:44
*** (1) TRANSACTION:
TRANSACTION 1BBDC37C, ACTIVE 0 sec, process no 4286, OS thread id 1257621824 starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s)
MySQL thread id 118809, query id 1200694 10.255.254.49 writeuser Sending data
update  tag set tag_count=(select count(distinct(product_id)) from tag_map where customer_id='89127' and tag_id='1369642') where tag_id='1369642'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7229 page no 19242 n bits 320 index `PRIMARY` of table `wishdb_1`.`tag_map` trx id 1BBDC37C lock mode S locks rec but not gap waiting
Record lock, heap no 247 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 80000000003b01b4; asc      ;  ;;
 1: len 6; hex 00001bbdc37b; asc      {;;
 2: len 7; hex 080000020805f6; asc        ;;
 3: len 8; hex 800000000014e62a; asc        *;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000015c27; asc       \';;
 6: len 8; hex 8000124efaa9d54c; asc    N   L;;


*** (2) TRANSACTION:
TRANSACTION 1BBDC37B, ACTIVE 0 sec, process no 4286, OS thread id 1254693184 updating or deleting, thread declared inside InnoDB 497
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 251 row lock(s), undo log entries 2
MySQL thread id 118798, query id 1200693 10.255.254.49 writeuser updating
delete from tag_map where customer_id=89127 and product_id in (2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7229 page no 19242 n bits 320 index `PRIMARY` of table `wishdb_1`.`tag_map` trx id 1BBDC37B lock_mode X locks rec but not gap
Record lock, heap no 246 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 80000000003b01b3; asc      ;  ;;
 1: len 6; hex 00001bbdc37b; asc      {;;
 2: len 7; hex 080000020805ad; asc        ;;
 3: len 8; hex 800000000014e626; asc        &;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000015c27; asc       \';;
 6: len 8; hex 8000124efaa9d54c; asc    N   L;;


Record lock, heap no 247 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 80000000003b01b4; asc      ;  ;;
 1: len 6; hex 00001bbdc37b; asc      {;;
 2: len 7; hex 080000020805f6; asc        ;;
 3: len 8; hex 800000000014e62a; asc        *;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000015c27; asc       \';;
 6: len 8; hex 8000124efaa9d54c; asc    N   L;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7229 page no 17066 n bits 824 index `IX_Tag_id` of table `wishdb_1`.`tag_map` trx id 1BBDC37B lock_mode X locks rec but not gap waiting
Record lock, heap no 547 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000014e62a; asc        *;;
 1: len 8; hex 80000000003b01b4; asc      ;  ;;


*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 1BD99F53
Purge done for trx's n:o < 1BD99F19 undo n:o < 0
History list length 606
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1BD99F38, not started, process no 4286, OS thread id 1268803904
MySQL thread id 175431, query id 4619492 10.255.254.49 writeuser
---TRANSACTION 1BD99E7A, not started, process no 4286, OS thread id 1266407744
MySQL thread id 175416, query id 4619188 10.255.254.49 writeuser
---TRANSACTION 1BD99E7B, not started, process no 4286, OS thread id 1265875264
MySQL thread id 175415, query id 4619190 10.255.254.49 writeuser
---TRANSACTION 1BD99DD6, not started, process no 4286, OS thread id 1252829504
MySQL thread id 175413, query id 4618846 10.255.254.49 writeuser
---TRANSACTION 0, not started, process no 4286, OS thread id 1268005184
MySQL thread id 175404, query id 4619533 localhost root
show engine innodb status
---TRANSACTION 1BD99F51, not started, process no 4286, OS thread id 1261615424
MySQL thread id 175398, query id 4619520 10.255.254.49 writeuser
---TRANSACTION 1BD99DF7, not started, process no 4286, OS thread id 1262414144
MySQL thread id 175397, query id 4618910 10.255.254.49 writeuser
---TRANSACTION 1BD99DA2, not started, process no 4286, OS thread id 1269336384
MySQL thread id 175396, query id 4619147 10.255.254.49 writeuser
---TRANSACTION 1BD98DA9, not started, process no 4286, OS thread id 1263745344
MySQL thread id 175375, query id 4616931 localhost root
---TRANSACTION 1BD99A0A, not started, process no 4286, OS thread id 1256290624
MySQL thread id 175364, query id 4617046 10.255.254.49 writeuser
---TRANSACTION 1BD99D4F, not started, process no 4286, OS thread id 1247504704
MySQL thread id 175363, query id 4618633 10.255.254.49 writeuser
---TRANSACTION 1BD99F17, not started, process no 4286, OS thread id 1262147904
MySQL thread id 175362, query id 4619452 10.255.254.49 writeuser
---TRANSACTION 1BD99EBB, not started, process no 4286, OS thread id 1270933824
MySQL thread id 175354, query id 4619309 10.255.254.49 writeuser
---TRANSACTION 1BD99E23, not started, process no 4286, OS thread id 1270667584
MySQL thread id 175353, query id 4619063 10.255.254.49 writeuser
---TRANSACTION 1BD99C23, not started, process no 4286, OS thread id 1246439744
MySQL thread id 175350, query id 4618133 10.255.254.49 writeuser
---TRANSACTION 1BD99CDB, not started, process no 4286, OS thread id 1260284224
MySQL thread id 175347, query id 4618383 10.255.254.49 writeuser
---TRANSACTION 1BD99E4D, not started, process no 4286, OS thread id 1253361984
MySQL thread id 175345, query id 4619069 10.255.254.49 writeuser
---TRANSACTION 1BD99E25, not started, process no 4286, OS thread id 1266940224
MySQL thread id 175338, query id 4619068 10.255.254.49 writeuser
---TRANSACTION 1BD99A2C, not started, process no 4286, OS thread id 1269070144
MySQL thread id 175343, query id 4617047 10.255.254.49 writeuser
---TRANSACTION 1BD99E48, not started, process no 4286, OS thread id 1251232064
MySQL thread id 175330, query id 4619059 10.255.254.49 writeuser
---TRANSACTION 1BD99EB7, not started, process no 4286, OS thread id 1269602624
MySQL thread id 175344, query id 4619303 10.255.254.49 writeuser
---TRANSACTION 1BD99F4F, not started, process no 4286, OS thread id 1252030784
MySQL thread id 175340, query id 4619518 10.255.254.49 writeuser
---TRANSACTION 1BD995D7, not started, process no 4286, OS thread id 1259219264
MySQL thread id 175336, query id 4615601 10.255.254.49 writeuser
---TRANSACTION 1BD99C7E, not started, process no 4286, OS thread id 1266673984
MySQL thread id 175339, query id 4618191 10.255.254.49 writeuser
---TRANSACTION 1BD99E29, not started, process no 4286, OS thread id 1260550464
MySQL thread id 175333, query id 4619071 10.255.254.49 writeuser
---TRANSACTION 1BD99D58, not started, process no 4286, OS thread id 1246173504
MySQL thread id 175331, query id 4618637 10.255.254.49 writeuser
---TRANSACTION 1BD99DD5, not started, process no 4286, OS thread id 1250433344
MySQL thread id 175329, query id 4618845 10.255.254.49 writeuser
---TRANSACTION 1BBDD27C, not started, process no 4286, OS thread id 1255225664
MySQL thread id 172105, query id 1497895 192.168.96.28 writeuser
---TRANSACTION 0, not started, process no 4286, OS thread id 1245108544
MySQL thread id 172041, query id 1497893 192.168.96.28 writeuser
---TRANSACTION 0, not started, process no 4286, OS thread id 1256024384
MySQL thread id 172023, query id 1497457 192.168.96.28 writeuser
---TRANSACTION 1BD99F52, ACTIVE 17 sec, process no 4286, OS thread id 1244576064 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175351, query id 4619521 10.255.254.49 writeuser update
insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('bian13','49382',1,now(),now())
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7232 page no 6469 n bits 112 index `PRIMARY` of table `wishdb_2`.`tag` trx id 1BD99F52 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


------------------
---TRANSACTION 1BD99F4E, ACTIVE 17 sec, process no 4286, OS thread id 1271732544
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175324, query id 4619517 10.255.254.49 writeuser
---TRANSACTION 1BD99F4D, ACTIVE 17 sec, process no 4286, OS thread id 1266141504
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175334, query id 4619515 10.255.254.49 writeuser
---TRANSACTION 1BD99F36, ACTIVE 17 sec, process no 4286, OS thread id 1267738944 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175349, query id 4619488 10.255.254.49 writeuser update
insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('1q29','50214',1,now(),now())
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7228 page no 6104 n bits 224 index `PRIMARY` of table `wishdb_1`.`tag` trx id 1BD99F36 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


------------------
---TRANSACTION 1BD99F29, ACTIVE 17 sec, process no 4286, OS thread id 1268537664 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175327, query id 4619473 10.255.254.49 writeuser update
insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('df55','37916',1,now(),now())
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7228 page no 6104 n bits 224 index `PRIMARY` of table `wishdb_1`.`tag` trx id 1BD99F29 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


------------------
---TRANSACTION 1BD99F25, ACTIVE 17 sec, process no 4286, OS thread id 1264011584
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175346, query id 4619466 10.255.254.49 writeuser
---TRANSACTION 1BD99F24, ACTIVE 17 sec, process no 4286, OS thread id 1264544064
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175366, query id 4619465 10.255.254.49 writeuser
---TRANSACTION 1BD99F22, ACTIVE 17 sec, process no 4286, OS thread id 1249900864 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175325, query id 4619460 10.255.254.49 writeuser update
insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('dy76','47121',1,now(),now())
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7228 page no 6104 n bits 224 index `PRIMARY` of table `wishdb_1`.`tag` trx id 1BD99F22 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


------------------
---TRANSACTION 1BD99F21, ACTIVE 17 sec, process no 4286, OS thread id 1258953024
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175332, query id 4619458 10.255.254.49 writeuser
---TRANSACTION 1BD99F20, ACTIVE 17 sec, process no 4286, OS thread id 1254426944
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175326, query id 4619457 10.255.254.49 writeuser
---TRANSACTION 1BD99F1E, ACTIVE 17 sec, process no 4286, OS thread id 1270135104 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175335, query id 4619451 10.255.254.49 writeuser update
insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('39','37077',1,now(),now())
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7232 page no 6469 n bits 112 index `PRIMARY` of table `wishdb_2`.`tag` trx id 1BD99F1E lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


------------------
---TRANSACTION 1BD99F1D, ACTIVE 17 sec, process no 4286, OS thread id 1270401344
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175352, query id 4619447 10.255.254.49 writeuser
---TRANSACTION 1BD99F1C, ACTIVE 17 sec, process no 4286, OS thread id 1249634624
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 175414, query id 4619446 10.255.254.49 writeuser
---TRANSACTION 1BD99EF7, ACTIVE 17 sec, process no 4286, OS thread id 1250699584
6 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1
MySQL thread id 175341, query id 4619385 10.255.254.49 writeuser
---TRANSACTION 1BD99EF3, ACTIVE 17 sec, process no 4286, OS thread id 1264277824
6 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1
MySQL thread id 175328, query id 4619380 10.255.254.49 writeuser
---TRANSACTION 1BD99ECD, ACTIVE 17 sec, process no 4286, OS thread id 1257621824 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 175342, query id 4619335 10.255.254.49 writeuser update
insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('b618','42110',1,now(),now())
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7228 page no 6104 n bits 224 index `PRIMARY` of table `wishdb_1`.`tag` trx id 1BD99ECD lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


------------------
---TRANSACTION 1BD99E92, ACTIVE 17 sec, process no 4286, OS thread id 1253628224 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 175365, query id 4619227 10.255.254.49 writeuser update
insert into tag(tag_name,customer_id,tag_count,creation_date,last_change_date)values('vf50','49378',1,now(),now())
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7232 page no 6469 n bits 112 index `PRIMARY` of table `wishdb_2`.`tag` trx id 1BD99E92 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


------------------
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
131213 OS file reads, 473402 OS file writes, 8601 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 3.35 writes/s, 0.29 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 25664, seg size 25666, 5 merges
merged operations:
 insert 76, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 42499631, node heap has 1134 buffer(s)
226.93 hash searches/s, 155.29 non-hash searches/s
---
LOG
---
Log sequence number 523089983604
Log flushed up to   523089983604
Last checkpoint at  523089983604
0 pending log writes, 0 pending chkp writes
420559 log i/o's done, 0.85 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21978152960; in additional pool allocated 0
Dictionary memory allocated 114560
Buffer pool size   1310719
Free buffers       1172077
Database pages     137508
Old database pages 50742
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 147, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 130799, created 6709, written 50946
0.00 reads/s, 0.00 creates/s, 2.44 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 137508, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 4286, id 1244043584, state: waiting for server activity
Number of rows inserted 4300116, updated 153217, deleted 49587, read 80513551
0.59 inserts/s, 0.26 updates/s, 0.00 deletes/s, 283.52 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


1 row in set (0.00 sec)


ERROR: 
No query specified


蓝冰咖啡
关注
专栏目录
行级锁，表级锁，乐观锁，悲观锁
Lei_5320的博客
07-16 482
名词解释 表级锁（锁定整个表） 行级锁（锁定一行） 悲观锁（抽象性，不真实存在这个锁） 乐观锁（抽象性，不真实存在这个锁） 1.表级锁 表级锁，一般是指表结构共享锁锁，是不可对该表执行DDL操作，但对DML操作都不限制。行级锁之前需要先加表结构共享锁。 如果用表级锁，其他客户将不能进行查询操作，因此开发中记得用行级锁 2.行级锁 行级锁，一般是指排它锁，即被锁定行不可进行修改，删除，只可以被其他会话select。行级锁之前需要先加表结构共享锁。 联系：在 Mysql 中，行级锁并不是直接
MySQL锁详解
10-23
MyISAM和MEMORY存储引擎使用表级锁，BDB存储引擎支持页面锁，同时也可以使用表级锁，而InnoDB存储引擎则同时支持行级锁和表级锁，默认采用行级锁。 表级锁具有开销小，加锁速度快的特点，不会出现死锁，但是锁定...
详细介绍MySQL/MariaDB的锁
weixin_33878457的博客
05-05 176
官方手册：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html 1.事务提交的方式 在MariaDB/MySQL中有3种事务提交的方式。 1.显式开启和提交。 使用begin或者start transaction来显式开启一个事务，显式开启的事务必须使用commit或者rollback...
【MySQL进阶篇】锁：全局锁、表级锁以及行级锁
最新发布
2401_83283514的博客
07-26 1706
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须要解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。因此，锁对数据库尤为重要，也更加复杂。
MySQL 行级锁（行锁、临键锁、间隙锁）
weixin_44300779的博客
02-11 1万+
三、行级锁 行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。 InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。 对于行级锁，主要分为以下三类： 1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。 2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行i
行级锁的概念
a752392的博客
12-31 210
行级锁只对用户正在访问的行进行锁定。 如果该用户正在修改某行，那么其他用户就可以更新同一表中该行之外的数据。 行级锁是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。 转载于:https://www.cnblogs.com/mooreliu/p/4850036.html...
MySQL之锁-行级锁
weixin_52690231的博客
03-15 1954
MySQL之锁-行级锁
行级锁
u012461550的专栏
01-11 966
行级锁（Row-level Lock） 先看一张图: 上图中，左半部分代表一个数据块（Data Block），而后半部分代表的是回滚段（Undo Segment）。 我们知道数据块是Oracle最基本的存储单元，而每个数据块都分成两部分：数据块头和数据块体（上图中，左上部分代表数据块头，左下部分代表数据块体）。数据块头存放的是一些管理信息，和事务有关的部分就是ITL（Intere
MySQL：锁机制.pdf
04-19
在使用行级锁时，可以创建InnoDB类型的表，然后通过SELECT ... FOR UPDATE对特定行进行加锁。行锁涉及的案例分析可以参考具体的SQL演示和操作，例如通过建表、插入数据、执行锁定等操作来观察行锁的行为。 总结来说...
分布式锁三种实现方式及对比
12-14
ZooKeeper提供了高可用性，但写操作需在 Leader 上完成，可能导致性能瓶颈。 **对比分析**： - **数据库分布式锁**：缺点在于数据库操作性能较低，可能锁表，非阻塞操作失败后需要轮询，可能导致连接资源占用...
深入理解MySQL数据库锁机制：避免死锁和性能瓶颈
![深入理解MySQL数据库锁机制：避免死锁和性能瓶颈]...在MySQL数据库中，锁机制通过各种锁类型和获取方式来实现，包括表级锁、行级锁和其他锁类型。 # 2
Oracle与sql简单优化与锁机制浅析.ppt
11-21
Oracle支持多种类型的锁，如行级锁（Row Locks）、表级锁（Table Locks）、多粒度锁定（Multigranularity Locking）等。行级锁允许不同事务同时访问不同的行，提高并发性能。表级锁则一次性锁定整个表，适用于低并发...
mysql锁机制之行级锁
热门推荐
liujie379908的博客
05-31 1万+
锁是在执行多线程时用于强行限定资源访问的同步机制，数据库锁根据锁的粒度可分为行级锁，表级锁和页级锁行级锁行级锁是mysql中粒度最细的一种锁机制，表示只对当前所操作的行进行加锁，行级锁发生冲突的概率很低，其粒度最小，但是加锁的代价最大。行级锁分为共享锁和排他锁。特点：开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最大，并发性也高；实现原理：InnoDB行锁是通过给索引项加锁来实现的，这...
oracle 行级锁
lxlj2006的专栏
11-09 3798
<br />DML事务锁定的机制<br />锁定能够保证当某个用户正在更新表里的一行数据时，其他用户不能同时更新相同的数据行，而且也不能删除或修改被更新的表。<br />锁定分为两种级别：行级别（TX锁）和表级别（TM锁）。<br /> 行级锁（TX锁）<br />假设某个用户（假设为A）发出如下的语句更新一条记录：<br />SQL> update employees set last_name='HanSijie' where employee_id=100; <br />      Oracle在对
MySQL中的行级锁
个人专长：数据结构和算法、大数据、数据库、主流框架
06-13 7584
学习林老师大作，醍醐灌顶，面试高频
行级锁表级锁总结
tujunlan的专栏
03-24 250
1、DML锁包括行级锁（TX）和表级锁（TM） 2、行级锁不是单独存在的，当事务获得了某些数据行上的行级锁时，此事务同时获得了数据行所属表上的表级锁，因为表级锁能够防止系统中并发地执行有冲突的 DDL 操作，避免当前事务中的数据操作被并发地DDL 操作影响。 3、表级锁分为5种，限制程度升序排列：    （1）行共享（row share）：与行排他类似，区别是别的事务还可以在此表上加任何排他...
MySQL数据库——锁-行级锁（行锁、间隙锁和临键锁）
li13437542099的博客
12-22 2608
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
mysql 锁级别说明 一
yinjinshui的博客
09-29 1726
1.锁的类型 MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。 锁级别 存储引擎 表级别（table-level） MyISAM，MEMORY，CSV 行级别（row-level） inndb 页级别（page-level） bdb 2.锁类型说明 2.1表级锁定（table-level） 表级别的锁定是MySQL各存储引擎中最大颗粒...
MySQL锁机制详解：表级锁、行级锁与页面锁
本文将深入探讨MySQL中的三种主要锁机制：表级锁、行级锁和页面锁，并重点关注MyISAM和InnoDB存储引擎的锁特性。 1. 表级锁：在MyISAM和MEMORY存储引擎中，表级锁是最常见的锁类型。这种锁机制一次性锁定整个表，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值