Not so long ago Solid released solidDB for MySQL Beta 3 so I decided now is time to take a bit closer look on new transactional engine for MySQL. While my far goal is the performance and scalability testing before I wanted to look at basic transactional properties such as deadlock detection, select for update handling and phantom reads in the repeatable read isolation level.
Solid has OPTMISTIC (default) and PESSIMISTIC concurrency control, so it was interesting to test both.
We used default isolation mode which is REPEATABLE-READ for this test.
Test 1: Solid, deadlock detection, default (OPTIMISTIC) concurrency control.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE `test2` ( `id` int(11) NOT NULL, `names` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=solidDB DEFAULT CHARSET=latin1 insert into test2 values (1,'Mysql'),(2,'Solid'),(3,'MyISAM'); mysql> begin; mysql> update test2 set names='mysql' where id=1; Session 2: mysql> begin; mysql> update test2 set names='Solid' where id=2; Session 1: mysql> update test2 set names='solid1' where id=2; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
So instead of waiting on row with id=2 to be unlocked by transaction in session 1 with OPTIMISTIC concurrency Solid simply reports deadlock due to conflicting update. For applications with rare update conflicts this actually may be good because it is simplier – you do not have to deal with complicated lock graphs or rely on timeout for deadlock resolution. For workloads with frequent row waits this however would result in increased number of deadlocks.
Test 2: SELECT FOR UPDATE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id =2 for update; +----+-------+ | id | names | +----+-------+ | 2 | Solid | +----+-------+ 1 row in set (0.00 sec) Session 2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id=2; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
As we can see deadlock happens in this case even for transaction which runs plain select. Behavior shown in this case does not match the one you would expect from multi versining engine – all the meaning of multi versioning is to allow you to perform consistent read even if rows are locked or even modified unless you require locking read, in which case SELECT FOR UPDATE or SELECT … LOCK IN SHARE MODE can be used. Quite possibly it is a bug which will be fixed in the future, if not it will be serious handycap.
Too bas Solid does not yet have lock investigation tool as SHOW INNODB STATUS (at least) – so it is hard to tell what is really happening.
Test 3: Phantom rows:
|
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 |
Table before test: select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) Session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) Session1: mysql> insert into test2 values (4,'HEAP'); Query OK, 1 row affected (0.00 sec) Session2: mysql> select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) Session1: mysql> commit; Query OK, 0 rows affected (0.01 sec) Session2: mysql> select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) |
So there are no phantom rows which is what we would expect from repeatable-read isolation mode, at least in its interpretation by Innodb.
Let’s look how PESIMISTIC works (mysqld started with –soliddb_pessimistic).
Test 1: Deadlock detection:
|
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 |
Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test2 set names='mysql' where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test2 set names='Solid' where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Session1: mysql> update test2 set names='solid1' where id = 2; ...waiting... Session2: mysql> update test2 set names='mysql1' where id=1; I would expect deadlock here - but not - just waiting... In 30 sec Session1: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
So as we can see Solid does not have instant deadlock detection as Innodb and have to rely on timeouts instead. This may be serious problem for some applications, especially interactive ones (ie web) – instead of getting quick deadlock and retrying transaction or giving error to the user you’re forced to wait. Setting lock wait timeout to some small value is not optimal solution ether as it would make some transactions to be terminated even though they just had normal lock wait not a deadlock.
Test 2: SELECT FOR UPDATE:
|
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 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id=2 for update; +----+---------+ | id | names | +----+---------+ | 2 | SolidDB | +----+---------+ 1 row in set (0.00 sec) Session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id=2; ..waiting.. Session1: mysql> commit; Session2: +----+---------+ | id | names | +----+---------+ | 2 | SolidDB | +----+---------+ 1 row in set (6.11 sec) |
Result well matches one which we got with OPTIMISTIC locking – just instead of simply balling out on first row lock conflict, query waits for rows to be unlocked. But wait why is it waiting if it suppose to be multi versioning system ?
Test 3: Phantom rows:
|
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 |
before test: mysql> select * from test2; +----+-----------+ | id | names | +----+-----------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+-----------+ Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) Session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) Session1: mysql> insert into test2 values (4,'HEAP'); Query OK, 1 row affected (0.00 sec) Session2: mysql> select * from test2; ...waiting... Session1: commit; Session2: +----+-----------+ | id | names | +----+-----------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | | 4 | HEAP | <- phantom +----+-----------+ 4 rows in set (4.25 sec) show variables like 'tx%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) |
In this case we get phantom row, which looks like double bug to us. First we should not get phantom row in repeatable-read isolation mode, second query results should not be different in OPTIMISTIC vs PESSIMISTIC concurrency mode. We can get deadlocks differently but query results should be the same.
Test 4: Update Handling:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) Session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) Session1: mysql> update test2 set names='SolidDB' where id=2; ... waiting... in 30 sec: ERROR 1031 (HY000): Table storage engine for 'test2' doesn't have this option |
Strange error message. This is probably one more bug which hopefully be resolved in further versions.
Test 5: UPDATE / SELECT FOR UPDATE in OPTIMISTIC mode
|
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 |
Solid optimistic: Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) Session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id=2; +----+----------+ | id | names | +----+----------+ | 2 | SolidDB1 | +----+----------+ 1 row in set (0.00 sec) Session1: mysql> select * from test2 where id=2 for update; +----+----------+ | id | names | +----+----------+ | 2 | SolidDB1 | +----+----------+ 1 row in set (0.00 sec) mysql> update test2 set names='SolidDB2' where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Session2: mysql> select * from test2 where id=2; +----+----------+ | id | names | +----+----------+ | 2 | SolidDB1 | +----+----------+ 1 row in set (0.01 sec Session1: mysql> commit; Query OK, 0 rows affected (0.01 sec) Please note transaction 1 was commited. Session2: mysql> select * from test2 where id=2 for update; +----+----------+ | id | names | +----+----------+ | 2 | SolidDB1 | +----+----------+ 1 row in set (0.00 sec) In this case we can se Multi-versioning in action, so it seems to work in some cases. However behavior is different from Innodb which will do READ-COMMITED for locking reads (SELECT FOR UPDATE/LOCK IN SHARE MODE) as only actual row versions can be locked. With Innodb we would get "SolidDB2" as result. mysql> update test2 set names='SolidDB3' where id=2; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
Error message is obviously wrong in this case. It is not deadlock but update conflict – other transaction has already modified row and commited so update could not proceed.
Also in this case we can see SELECT FOR UPDATE does not play nicely with OPTIMISTIC concurrency. Generally you would use this statement to ensure rows are locked at once so transaction could proceed modifying them without causing deadlocks.
For comparion here are InnoDB sessions:
Test 1: Deadlock detection (deadlock is correctly detected):
|
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 |
insert into test2 values (1,'Mysql'),(2,'Solid'),(3,'MyISAM'); Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test2 set names='mysql' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Session2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test2 set names='Solid' where id=2; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 Session1: mysql> update test2 set names='solid1' where id=2; ...waiting... Session2: mysql> update test2 set names='mysql2' where id=1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Session1: Query OK, 1 row affected (9.65 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
Test 2: SELECT FOR UPDATE (second session is not blocked):
|
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 |
Session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id=2 for update; +----+-------+ | id | names | +----+-------+ | 2 | Solid | +----+-------+ 1 row in set (0.00 sec) Session2: mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) |
Test 3: Phantom rows (no phantom rows in repeatable-reads):
|
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 |
Session1: mysql> begin; Sessino2: mysql> begin; Session1: mysql> insert into test2 values (4,'FEDERATED'); Session2: select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) Session1: mysql> commit; Session2: mysql> select * from test2; +----+--------+ | id | names | +----+--------+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +----+--------+ 3 rows in set (0.00 sec) |
Conclusion: Current Beta version seems to have number of bugs when it comes to concurrency control, so it is hard to say what we’ll see in final version. Current behavior is however very different from Innodb in many cases which may make porting applications to use SolidDB instead of Innodb complicated.
P.S.
After previous experiments I tried SolidDB under sysbench and got next (1 client):
|
1 2 3 4 5 6 7 8 9 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE sbtests set k=k+1 where id=401692; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE sbtests set c='773425598-640282370-898784553-626619839-555294498-279281971-788986238-202873246-188128003-162592967' where id=496201; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
So we can’t run SysBench benchmark with current version. Hopefully next beta will be solid enough to run benchmark.
