WordPress: 트랜잭션이 없는 일부 쿼리의 교착 상태?

WordPress: 트랜잭션이 없는 일부 쿼리의 교착 상태?

최근에 트래픽이 증가하는 동안 다음과 같은 PHP 오류가 발생하기 시작했습니다.

Got error 'PHP message: Deadlock found when trying to get lock; try restarting transaction
INSERT INTO `wp_48_cf7dbplugin_submits` (`submit_time`, `form_name`, `field_name`, `field_value`, `field_order`) VALUES ('1641679333.361248', 'order', 'y_flat', '10', '8')

require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, WPCF7_REST_Controller->create
_feedback, WPCF7_ContactForm->submit, WPCF7_Submission::get_instance, WPCF7_Submission->proceed, WPCF7_Submission->before_send_mail, do_action_ref_array('wpcf7_before_send_mail'), WP_Hook->do_action, WP_Hook->apply_filters, CFDBIntegrationContactForm7->saveFormData, CF7DBPlugin->saveFormData

그리고 이것은 모든 것은 아니지만 몇 가지 더 많은 질문에 대해서도 계속됩니다.WPCF7DB 플러그인이 있는 워드프레스입니다.

확인해보니 워드프레스나 플러그인에 거래가 전혀 없습니다.

플러그인은 각 필드를 반복한 다음 단순을 사용합니다.wpdb::prepare그리고.wpdb::query조합:

$parametrizedQuery = "INSERT INTO `$tableName` (`submit_time`, `form_name`, `field_name`, `field_value`, `field_order`) VALUES (%s, %s, %s, %s, %s)";

$wpdb->query($wpdb->prepare($parametrizedQuery,
  $time,
  $title,
  $nameClean,
  $valueClean,
  $order++));
}

테이블 구조:

CREATE TABLE `wp_48_cf7dbplugin_submits` (
  `submit_time` decimal(16,6) NOT NULL,
  `form_name` varchar(127) CHARACTER SET utf8 DEFAULT NULL,
  `field_name` varchar(127) CHARACTER SET utf8 DEFAULT NULL,
  `field_value` longtext CHARACTER SET utf8 DEFAULT NULL,
  `field_order` int(11) DEFAULT NULL,
  `file` longblob DEFAULT NULL,
  KEY `submit_time_idx` (`submit_time`),
  KEY `form_name_idx` (`form_name`),
  KEY `field_name_idx` (`field_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

매번 다른 루프의 일부 필드에서만 발생한다는 것이 이상한 일입니다.

데이터베이스 엔진은 PHP 7.4를 사용하는 5.5.5-10.5.9-MariaDB-1:10.5.9+maria~buster입니다.

당신은 무엇이 이것을 유발하는지 알고 있습니까?

엔진 상태 보고서:

=====================================
2022-01-10 13:27:13 0x7fd4b0066700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 69 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 95869 srv_active, 0 srv_shutdown, 20734661 srv_idle
srv_master_thread log flush and writes: 20829772
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 30716
OS WAIT ARRAY INFO: signal count 16274
RW-shared spins 274138, rounds 2338467, OS waits 886
RW-excl spins 952, rounds 16506, OS waits 360
RW-sx spins 102, rounds 1188, OS waits 34
Spin rounds per wait: 8.53 RW-shared, 17.34 RW-excl, 11.65 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 4055001
Purge done for trx's n:o < 4055001 undo n:o < 0 state: running
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422025324769496, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending normal aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
37144192 OS file reads, 2402390 OS file writes, 2249175 OS fsyncs
21.38 reads/s, 16372 avg bytes/read, 1.22 writes/s, 1.28 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 17, free list len 77, seg size 95, 3690 merges
merged operations:
 insert 136065, delete mark 309, delete 171
discarded operations:
 insert 9389, delete mark 13, delete 13
0.00 hash searches/s, 4840.34 non-hash searches/s
---
LOG
---
Log sequence number 5800939056
Log flushed up to   5800939056
Pages flushed up to 5773682207
Last checkpoint at  5773682207
0 pending log flushes, 0 pending chkp writes
2147483 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 3290232
Buffer pool size   8065
Free buffers       0
Database pages     8065
Old database pages 2957
Modified db pages  2027
Percent of dirty pages(LRU & free pages): 25.130
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 1979022, not young 1458024765
21.80 youngs/s, 2893.80 non-youngs/s
Pages read 37142927, created 257902, written 250427
21.38 reads/s, 0.06 creates/s, 0.00 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 1 / 1000 not 149 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8065, unzip_LRU len: 0
I/O sum[1474]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 26238521, updated 97796, deleted 3496, read 1479166152
1.13 inserts/s, 0.01 updates/s, 0.00 deletes/s, 4830.65 reads/s
Number of system rows inserted 48, updated 0, deleted 48, read 101
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

이러한 가짜 교착 상태에 대한 해결책은 테이블의 기본 자동 증분 키를 만드는 것이었습니다.

ALTER TABLE wp_48_cf7dbplugin_submits ADD id int NOT NULL AUTO_INCREMENT primary key;

언급URL : https://stackoverflow.com/questions/70637084/wordpress-deadlock-on-some-queries-without-transaction