поставил mysql локально 5.6.39, не работают даже совсем примитивные транзакции.
таблица
1.
2.
3.
4.
5.
CREATE TABLE test1 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
транзакция 1
1.
2.
3.
4.
5.
6.
7.
8.
9.
mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.00 sec)
транзакция 2
1.
2.
3.
4.
5.
6.
7.
8.
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.00 sec)
теперь в транзакции 1 удаляем ту самую строку какую вставила эта транзакция
1.
mysql> delete from test1 where name = 'shit1' ;
транзакция 1 уже повисает на блокировке
теперь в транзакции 2 удаляем ее строку
1.
2.
mysql> delete from test1 where name='shit2' ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
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.
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-21 19:00:51 7f61fce4a700
*** (1) TRANSACTION:
TRANSACTION 2010, ACTIVE 76 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 14, OS thread handle 0x7f61fce8c700, query id 305 localhost root updating
delete from test1 where name = 'shit1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test1` trx id 2010 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000000007db; asc ;;
2: len 7; hex af000001ce0110; asc ;;
3: len 5; hex 7368697432; asc shit2;;
*** (2) TRANSACTION:
TRANSACTION 2011, ACTIVE 41 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 0x7f61fce4a700, query id 306 localhost root updating
delete from test1 where name='shit2'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 19 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test1` trx id 2011 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000000007db; asc ;;
2: len 7; hex af000001ce0110; asc ;;
3: len 5; hex 7368697432; asc shit2;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test1` trx id 2011 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000000007da; asc ;;
2: len 7; hex 2e000001d10110; asc . ;;
3: len 5; hex 7368697431; asc shit1;;
*** WE ROLL BACK TRANSACTION (2)
в mysql 8 чуток реже, но в принципе примерно ту же хрень получаю. как же так !?
