Конкретный вопрос на собеседовании, на который никто не может ответить

Sad Spirit

мизантроп (Старожил PHPClub)
Команда форума
А куда DiMA-то слился? Я всё от него жду "официального" решения по конкурентным INSERT'ам. А то получается, что он на собеседовании задаёт задачу либо сформулированную неправильно, либо на которую сам не знает ответа.

А товарищи счастливые пользователи MySQL не могли бы повторить мой опыт с двумя консолями и конкурентной вставкой? В одной-то понятно, работает.
 

Sad Spirit

мизантроп (Старожил PHPClub)
Команда форума
Автор оригинала: Sherman
Вроде whirlwind ответил(про gap lock) выше?
Ну тут ещё и вариант с INSERT / SELECT появился. Хотя если не работает с SELECT FOR UPDATE, то 90%, что и здесь те же яйцы в профиль.

-~{}~ 01.10.09 21:54:

Автор оригинала: Sherman
Эта тема является не просто источником нескончаемого получения лулзов, но отлично показывает, что "знание" mysql - это на самом деле, знание всяческих мелких хаков, фишичек и прочих затычек, которыми программисты mysql пытаются затыкать баги в своей СУБД, вместо того, чтобы, не изобретая велосипед, просто реализовывать стандарт.
...на самом деле тут ещё обсуждается одна из немногих ценных идей MySQL (вернее, не MySQL, а InnoDB), это вот этот самый gap locking. Который идёт чуть дальше, чем требует стандарт.
 

MiksIr

miksir@home:~$
Sad Spirit, с InnoDB работает - я проверил.
А составной запрос стопудова те же яйца в профиль - ну нельзя сохранить неизменность данных не делая блокировки. Просто из соображений здравого смысла. Т.е. или опять же gap lock или блокировка всей таблицы.
А DiMA просто не знал, видимо, что gap lock - это не у всех такое есть.
Вариант, который fisher первый озвучил - именованные локи с именем как функция от параметров - наиболее универсальный и гибкий вариант.

-~{}~ 01.10.09 22:02:

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html
 

whirlwind

TDD infected, paranoid
В общем, я думаю вывод из топа можно сделать следующий: для отказа от индексов должна быть не просто веская причина, а бляппцнаскоковывереннаяипросчитанная причина. Если по замерам select/insert с индексами реально отстает от select/insert без индексов, то надо ломать моск. Насколько часто встречается такая ситуация в реале и насколько оправдан данный вопрос в качестве вопроса на собеседовании, решает каждый самостоятельно.
 

algo

To the stars!
whirlwind
вижу, ты предлагал insert ... select, но опять же отзывов нет, не понимаю чем такой подзапрос плох имхо лучшее решение..

-~{}~ 01.10.09 22:31:

p.s. именованные локи - те же семафоры
 

Sad Spirit

мизантроп (Старожил PHPClub)
Команда форума
Автор оригинала: MiksIr
Вариант, который fisher первый озвучил - именованные локи с именем как функция от параметров - наиболее универсальный и гибкий вариант.
Да, пожалуй что такое и в Postgres'е работать будет через Advisory locks.

Но вообще вопрос сильно "на засыпку", ибо надо знать много неочевидных вещей. В жестокой реальности такое решается уникальным индексом по полям A и B и фигаченьем вставки в таблицу (возможно, с предварительно поставленным savepoint'ом, чтобы вся транзакция не откатилась) --- прокатит / не прокатит. Ну и потом update / select for update [nowait], если не прокатило.
 

crocodile2u

http://vbolshov.org.ru
Провел интересный, по-моему, эксперимент с insert ... select:

КОНСОЛЬ 1:
--
mysql> CREATE TABLE `a` (
`x` text,
`s` int(11) default NULL
) ENGINE=InnoDB;
mysql> insert into a (x, s) select 'y', sleep(10) from dual where not exists(select x from a where x='y');
--
КОНСОЛЬ 2:
mysql> insert into a (x) select 'y' from dual where not exists(select x from a where x='y');
--
Спустя какое-то время (очевидно, 10 секунд - время, нужное для переключения между консолями), в первой консоли:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

... а во второй:
Query OK, 1 row affected (7.35 sec)
Records: 1 Duplicates: 0 Warnings: 0

Как это проинтерпретировать, даже и не знаю :)
 

MiksIr

miksir@home:~$
Как это проинтерпретировать, даже и не знаю :)
Так, что insert .. (select), как я и говорил, работает точно так же, как и select for update - в том смысле, что ставит те же самые блокировки.
 

MiksIr

miksir@home:~$
Такое происходит при пустой таблице - блокируется не селект, а инсерт. Почему - это уже думать нужно, не в пятницу =)
 

Найч

Алгоритмик :-)
не воспроизводится

консоль 1

mysql> CREATE TABLE `a` (
-> `x` text,
-> `s` int(11) default NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into a (x, s) select 'y', sleep(10) from dual where not exists(select x from a where x='y');
Query OK, 1 row affected (10.00 sec)
Records: 1 Duplicates: 0 Warnings: 0


консоль 2
mysql> insert into a (x) select 'y' from dual where not exists(select x from a where x='y');
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0


mysql> select version();
+------------+
| version() |
+------------+
| 5.0.67-log |
+------------+
1 row in set (0.00 sec)
 

MiksIr

miksir@home:~$
Найч инсерт во 2-й консоли нужно делать до того, как проснется первая консоль
 

whirlwind

TDD infected, paranoid
Автор оригинала: Найч
не воспроизводится
аналогично

1 консоль

Код:
mysql> CREATE TABLE `a` (
    -> `x` text,
    -> `s` int(11) default NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into a (x, s) select 'y', sleep(10)
 from dual where not exists(select x from a where x='y');
ERROR 1213 (40001): Deadlock found when trying to get lock;
 try restarting transaction
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.0.75-0ubuntu10.2 | 
+--------------------+
1 row in set (0.00 sec)

mysql>
 

Найч

Алгоритмик :-)
MiksIr
спасибо, вот уж не догадался бы дедлок последовательно (читай - в 1 поток) пытаться воспроизводить
 

crocodile2u

http://vbolshov.org.ru
Я, к сожалению, только в понедельник смогу уточнить версию мускуля, которую использовал - но по-моему, она все же меньше, чем 5.0.67.

-~{}~ 03.10.09 08:22:

Вообще, насколько я понимаю, и вариант с дедлоком, и без него - приводят к результатам, которые противоречят условию задачи. Дедлок - ошибка, а в варианте Найч вставилось две записи с одинаковым х.
 

dr-sm

Новичок
Автор оригинала: crocodile2u
Провел интересный, по-моему, эксперимент с insert ... select:

КОНСОЛЬ 1:
--
mysql> CREATE TABLE `a` (
`x` text,
`s` int(11) default NULL
) ENGINE=InnoDB;
mysql> insert into a (x, s) select 'y', sleep(10) from dual where not exists(select x from a where x='y');
--
КОНСОЛЬ 2:
mysql> insert into a (x) select 'y' from dual where not exists(select x from a where x='y');
--
Спустя какое-то время (очевидно, 10 секунд - время, нужное для переключения между консолями), в первой консоли:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

... а во второй:
Query OK, 1 row affected (7.35 sec)
Records: 1 Duplicates: 0 Warnings: 0

Как это проинтерпретировать, даже и не знаю :)
Код:
mysql> show engine innodb status;
LATEST DETECTED DEADLOCK                                                                                                            
------------------------                                                                                                            
091003 18:05:04                                                                                                                     
*** (1) TRANSACTION:                                                                                                                
TRANSACTION 0 2310, ACTIVE 3 sec, process no 2712, OS thread id 2734521232 inserting                                                
mysql tables in use 2, locked 2                                                                                                     
LOCK WAIT 4 lock struct(s), heap size 320, 2 row lock(s)                                                                            
MySQL thread id 1, query id 27 localhost root executing                                                                             
insert into a (x) select 'y' from dual where not exists(select x from a where x='y')                                                
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                        
RECORD LOCKS space id 0 page no 60 n bits 72 [b]index `GEN_CLUST_INDEX` of table `test`.`a`[/b] trx id 0 2310 [b]lock_mode X[/b] 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;;                                                                                     

*** (2) TRANSACTION:
TRANSACTION 0 2309, ACTIVE 10 sec, process no 2712, OS thread id 2734320528 inserting, thread declared inside InnoDB 500
mysql tables in use 2, locked 2                                                                                         
4 lock struct(s), heap size 320, 2 row lock(s)                                                                          
MySQL thread id 2, query id 26 localhost root                                                                           
insert into a (x, s) select 'y', sleep(10) from dual where not exists(select x from a where x='y')                      
*** (2) HOLDS THE LOCK(S):                                                                                              
RECORD LOCKS space id 0 page no 60 n bits 72 [b]index `GEN_CLUST_INDEX` of table `test`.`a`[/b] trx id 0 2309 [b]lock mode S[/b]      
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0                                         
 0: len 8; hex 73757072656d756d; asc supremum;;                                                                         

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 60 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`a` trx id 0 2309 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;;                                                                                     

*** WE ROLL BACK TRANSACTION (2)
запрос со sleep'om почему-то обломался с lock escalation.

version - 5.1.38

судя по http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html все правильно.
1) транзакция (2) захватила shared lock select'ом и ушла спать.
2) транзакция (1) попыталась захватить exclusive lock и обломавшись из-за 1) встала в очередь
3) транзакция (2) проснулась и попыталась захватить exclusive lock и тк в очереди уже есть 2) => deadlock condition.
 

crocodile2u

http://vbolshov.org.ru
Давайте дружно позовем DiMA? Уж больно хочется услышать его комментарии. DiMA! Ау!
 

algo

To the stars!
Да, вроде, и так все понятно. Реакция на висящую транзакцию вполне ок, что в ней некорректного?
 
Сверху