Такая ситуация. Был запущен многопоточный нагрузочный тест, который выполнял некоторую stored функцию по добавлению записи в таблицу. Пул коннектов к базе персистентный. Есть 2 вопроса: один менее важный, другой более важный. Начну с менее важного:
1.
2.
---TRANSACTION 0 40774, not started, process no 13919, OS thread id 1166399808
MySQL thread id 126, query id 451234 localhost mysql_user
После завершения теста вместе с коннектами остались висеть транзакции в состоянии 'not started'. Почему так происходит? В базе автокоммит включен по-умолчанию. Это значит, что по завершении запроса транзакция должна закрываться. Как сказано в мануале , транзакция не закрывается только в случае SQL ошибок, не связанных с dead-локами и lock-таймаутами (опция innodb_rollback_on_timeout включена). Таких SQL ошибок у меня нет (за исключением одного "но", см ниже). Полно dead-локов и таймаутов, но они ведь должны откатывать целиком транзакцию.
Теперь более важный вопрос:
1.
2.
3.
---TRANSACTION 0 10088, ACTIVE 1768 sec, process no 13919, OS thread id 1170393408
32 lock struct(s), heap size 30704, undo log entries 3285
MySQL thread id 131, query id 452275 localhost mysql_user
Одна транзакция висит в состоянии 'ACTIVE'. Чего она ждёт и кто её держит? dead-лок с ней завершился и другая транзакция откатилась. Все остальные транзакции в состоянии 'not started'. Это значит, по идее, что ничего не должно её держать?
mysql> show engine innodb status;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Status |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TRANSACTION 0 10088, ACTIVE 1768 sec, process no 13919, OS thread id 1170393408
32 lock struct(s), heap size 30704, undo log entries 3285
MySQL thread id 131, query id 452275 localhost mysql_user---TRANSACTION 0 10088, ACTIVE 1768 sec, process no 13919, OS thread id 1170393408
32 lock struct(s), heap size 30704, undo log entries 3285
MySQL thread id 131, query id 452275 localhost mysql_user-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=====================================
130823 12:26:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 20441, signal count 10089
Mutex spin waits 0, rounds 845136, OS waits 16874
RW-shared spins 1682, OS waits 674; RW-excl spins 620, OS waits 369
------------------------
LATEST DETECTED DEADLOCK
------------------------
130823 12:09:40
*** (1) TRANSACTION:
TRANSACTION 0 10088, ACTIVE 750 sec, process no 13919, OS thread id 1170393408 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 32 lock struct(s), heap size 30704, undo log entries 3202
MySQL thread id 131, query id 446016 localhost mysql_user update
insert into table1 (value) values ( NAME_CONST('_value',_binary'RYEz)k6' COLLATE 'binary'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `db1/table1` trx id 0 10088 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 0 40305, ACTIVE 0 sec, process no 13919, OS thread id 1170127168 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1216, undo log entries 4
MySQL thread id 135, query id 445836 localhost mysql_user update
insert into table1 (value) values ( NAME_CONST('_value',_binary'kTFvf_\'' COLLATE 'binary'))
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `db1/table1` trx id 0 40305 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 193 n bits 168 index `value` of table `db1/table1` trx id 0 40305 lock mode S waiting
Record lock, heap no 88 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 7; hex 6b544676665f27; asc kTFvf_';; 1: len 4; hex 0000005c; asc \;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 40905
Purge done for trx's n:o < 0 902 undo n:o < 0 0
History list length 2
Total number of lock structs in row lock hash table 24
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 40904, not started, process no 13919, OS thread id 1085221184
MySQL thread id 138, query id 452719 localhost mysql_user
show engine innodb status
---TRANSACTION 0 40062, not started, process no 13919, OS thread id 1170659648
MySQL thread id 137, query id 448829 localhost mysql_user
---TRANSACTION 0 40774, not started, process no 13919, OS thread id 1166399808
MySQL thread id 126, query id 451234 localhost mysql_user
---TRANSACTION 0 40778, not started, process no 13919, OS thread id 1169860928
MySQL thread id 128, query id 451236 localhost mysql_user
---TRANSACTION 0 40832, not started, process no 13919, OS thread id 1171192128
MySQL thread id 132, query id 452335 localhost mysql_user
---TRANSACTION 0 40777, not started, process no 13919, OS thread id 1169328448
MySQL thread id 136, query id 451235 localhost mysql_user
---TRANSACTION 0 40820, not started, process no 13919, OS thread id 1170925888
MySQL thread id 134, query id 451746 localhost mysql_user
---TRANSACTION 0 40793, not started, process no 13919, OS thread id 1170127168
MySQL thread id 135, query id 451638 localhost mysql_user
---TRANSACTION 0 40813, not started, process no 13919, OS thread id 1166666048
MySQL thread id 133, query id 451621 localhost mysql_user
---TRANSACTION 0 40894, not started, process no 13919, OS thread id 1168529728
MySQL thread id 125, query id 452655 localhost mysql_user
---TRANSACTION 0 40902, not started, process no 13919, OS thread id 1167997248
MySQL thread id 122, query id 452710 localhost mysql_user
---TRANSACTION 0 40799, not started, process no 13919, OS thread id 1167198528
MySQL thread id 130, query id 451710 localhost mysql_user
---TRANSACTION 0 40842, not started, process no 13919, OS thread id 1168263488
MySQL thread id 127, query id 451985 localhost mysql_user
---TRANSACTION 0 38740, not started, process no 13919, OS thread id 1169062208
MySQL thread id 129, query id 429121 localhost mysql_user
---TRANSACTION 0 40775, not started, process no 13919, OS thread id 1167731008
MySQL thread id 124, query id 451233 localhost mysql_user
---TRANSACTION 0 40841, not started, process no 13919, OS thread id 1171458368
MySQL thread id 123, query id 451972 localhost mysql_user
---TRANSACTION 0 40852, not started, process no 13919, OS thread id 1167464768
MySQL thread id 121, query id 452102 localhost mysql_user
---TRANSACTION 0 40828, not started, process no 13919, OS thread id 1166932288
MySQL thread id 120, query id 451856 localhost mysql_user
---TRANSACTION 0 40850, not started, process no 13919, OS thread id 1168795968
MySQL thread id 119, query id 452078 localhost mysql_user
---TRANSACTION 0 38738, not started, process no 13919, OS thread id 1169594688
MySQL thread id 118, query id 429114 localhost mysql_user
---TRANSACTION 0 10088, ACTIVE 1768 sec, process no 13919, OS thread id 1170393408
32 lock struct(s), heap size 30704, undo log entries 3285
MySQL thread id 131, query id 452275 localhost mysql_user
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
0 OS file reads, 12295 OS file writes, 11751 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 12500953, used cells 1992, node heap has 4 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 55365176
Log flushed up to 0 55365176
Last checkpoint at 0 55365176
0 pending log writes, 0 pending chkp writes
11617 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 7014419434; in additional pool allocated 1047552
Buffer pool size 385536
Free buffers 381202
Database pages 4329
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 0, created 4329, written 5229
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 13919, id 1165867328, state: waiting for server activity
Number of rows inserted 16961, updated 2, deleted 0, read 36388
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> show processlist;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
+-----+-----------------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+------+---------+------+-------+------------------+
| 118 | mysql_user | localhost | db1 | Sleep | 2268 | | NULL |
| 119 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 120 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 121 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 122 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 123 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 124 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 125 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 126 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 127 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 128 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 129 | mysql_user | localhost | db1 | Sleep | 2268 | | NULL |
| 130 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 131 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 132 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 133 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 134 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 135 | mysql_user | localhost | db1 | Sleep | 2235 | | NULL |
| 136 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 137 | mysql_user | localhost | db1 | Sleep | 2238 | | NULL |
| 138 | mysql_user | localhost | db1 | Query | 0 | NULL | show processlist |
+-----+-----------------+-----------+------+---------+------+-------+------------------+
Если это поможет, то процедура добавления в таблицу выглядит так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
create function find_or_add(_value varbinary(255))
returns int unsigned
deterministic
modifies sql data
begin
declare id_ int unsigned;
# TODO: possible replace with continue handler?
declare exit handler for 1062
begin
select id from table1 where value = _value limit 1 into id_ for update;
return id_;
end;
# this select is not really necessary but possible improves performance
# where reads more than writes
select id from table1 where value = _value limit 1 into id_;
if found_rows() > 0 then
return id_;
end if;
insert into table1 (value) values (_value);
return last_insert_id();
end;;
Теперь об "но". Есть ошибка 1062 (duplicate-key error). Хэндлер её находится внутри этой функции. Как сказано в мануале, эта ошибка не откатывает транзакцию целиком. Но т.к. есть хэндлер, то функция по выходу должна делать коммит. Разве нет? |
