Методы выявления ошибок в SQL приложении

[p1lot]

Вопрос по локам возник.
Вот классический случай. Транзакция ждёт лока. Мы это видим так:

---TRANSACTION 0 2707548981, ACTIVE 8 sec, process no 10221, OS thread id 1164843328 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 1179 lock struct(s), heap size 194544, undo log entries 22840
MySQL thread id 9735008, query id 2678070880 server_ip connect_name updating
query
Trx read view will not see trx with id >= 0 2707548984, sees < 0 2707547862
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 262 page no 90699 n bits 216 index `PRIMARY` of table `table_name` 
trx id 0 2707548981 lock_mode X waiting
Record lock, heap no 125 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 800000008d7f9502; asc         ;; 1: len 6; hex 0000a161ef1c; asc    a  ;; 2: len 7; hex 8000010014011c; asc        ;; 3: len 4; hex 05e0bec9; asc     ;; 4: len 4; hex 9951d488; asc  Q  ;; 5: len 4; hex 4b0bc4f7; asc K   ;; 6: len 8; hex 4051a04fe44992bf; asc @Q O I  ;;

Как узнать transaction_id той транзакции, которая залочила ряды, которые ждёт исследуемая транзакция?

[svetasmirnova]

Анализом списка всех транзакций.

Например в Initial reply вот здесь: bugs.mysql.com/bug.php?id=45901 почти такой же вывод

----<q>----
---TRANSACTION 0 8984, ACTIVE 2 sec, OS thread id 2808 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 2, query id 34 localhost 127.0.0.1 root Sending data
select * from d where d2='002' for update
— TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 57 n bits 80 index `PRIMARY` of table `cd`.`d` trx id 0
8984 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 3; hex 313031; asc 101;; 1: len 6; hex 00000000230f; asc # ;; 2: len 7; hex
800000002d0110; asc — ;; 3: len 3; hex 303031; asc 001;;
4: len 2; hex 3131; asc 11;;
----</q>----

И ниже транзакция, которая держит lock

----<q>----
---TRANSACTION 0 8983, ACTIVE 30 sec, OS thread id 2408
4 lock struct(s), heap size 320, 7 row lock(s)
MySQL thread id 1, query id 30 localhost 127.0.0.1 root
----</q>----

[p1lot]

А что делать в случае, когда постоянно более сотни транзакций, и штук 30 из них держат какие-то локи? Чаще всего, конечно, они не пересекаются, но бывают и исключения, которые хочется поймать. Наверняка же можно как-то проследить какой процесс получил лок (наподобие flock'a можно lsof | grep). В том смысле что наверняка же лок знает кто его получил, чтобы именно от того процесса пропускать изменения. Не планируется ли создать какой-нибудь разумный инструмент\плагин, который сможет анализировать кто кого ждёт?

[svetasmirnova]

С InnoDB Plugin можно больше информации получить: www.innodb.com/doc/innodb_plugin-1.0/innodb-information-schema.html#innodb-information-schema-innodb_trx Он, кстати, есть в последних стандартных поставках 5.1. В обычной InnoDB, насколько я знаю, только путём анализа статуса и логов.

[outcoldman]

За работу вам безусловно плюс. Прочитав пару абзацев понял, что литература не для меня.

[svetasmirnova]

Спасибо :)

[pwlnw]

>net_read_timeout
>Сколько ждать ответа на запрос SELECT
>net_write_timeout
>Сколько ждать ответа на запрос, модифицирующий данные.

Вот здесь непонятно. Это работает? Вы что-то скрываете чего нет в документации или это ошибка? в документации смысл совсем другой.
Какие вообще есть способы заставить mysql прерывать длинные запросы по времени, кроме написания программы-монитора?

[svetasmirnova]

Прошу прощения за небыстрый ответ: личные дела.

> Вот здесь непонятно. Это работает? Вы что-то скрываете чего нет в документации или это ошибка?

Да, это ошибка. Спасибо за замечание!

> Какие вообще есть способы заставить mysql прерывать длинные запросы по времени, кроме написания программы-монитора?

Никаких. Впрочем, существует вот такой worklog: forge.mysql.com/worklog/task.php?id=1272

[svetasmirnova]

Исправила, спасибо за поправку:

net_read_timeout

Сколько времени MySQL сервер будет ждать ответа от клиента осуществляющего чтение. Например, клиент послал запрос SELECT и читает ответ сервера.

net_write_timeout

Сколько времени MySQL сервер будет ждать ответа от клиента осуществляющего запись. Например, клиент поылает запрос для обработки.

(http://sql-error.microbecal.com/chap9.html)

[pwlnw]

как-то вообще не так.
если рассматривать сервер как сетевое приложение без специфики sql, то понятнее получается:

net_read_timeout — время, которое сервер ожидает полную порцию данных от клиента. По достижении соединение закрывается. При этом между запросами работает другой таймаут — net_wait_timeout.

net_write_timeout — время, которое сервер ожидает завершения отправки (записи) порции данных клиенту.

причем, ни один из таймаутов не влияет на статус процесса-обработчика запроса. он будет продолжать выполнять работу по выборке строк, хотя их посылать и некуда.

[svetasmirnova]

> причем, ни один из таймаутов не влияет на статус процесса-обработчика запроса. он будет продолжать выполнять работу по выборке строк, хотя их посылать и некуда.

В общем так и есть. Вот примеры: bugs.mysql.com/bug.php?id=47311, bugs.mysql.com/bug.php?id=46103, bugs.mysql.com/bug.php?id=49641