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.
About the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
Vadim,
Thanks for taking the solidDB for MySQL Beta 3 for a test run. We have come a long way, and of course there is still a ways to go. We know that we have additional work to do on quality and functionality of our integration with the MySQL Server. I will address below the rest of the issues that you raised.
About the pessimistic locking, our release notes mention that we do not yet support pessimistic locking. However, we could have been clearer. We will improve the documentation before next release.
Regarding Test 2 for optimistic locking, thanks for catching the bug. It’s unfortunate that this bug slipped through our QA process. We have already fixed this in our latest code base, and the fix will show up in next release.
The suggestion to have “SHOW SOLIDDB STATUS” is a great suggestion. I will investigate our support for showing status information or try to get this functionality added in a future release.
I believe that you meant to place Test 5, which you have listed under the pessimistic cases, with the other optimistic locking cases. You are correct in noting that our error message is wrong. This will be fixed in the next release.
You are also correct in noting that our SELECT FOR UPDATE behaves differently from InnoDB. One thing to remember is that our Storage Engine for MySQL comes from our main code base, which has been commercially available since 1994 or so. That is the behavior of our core product (BoostEngine). However, you bring up an important point. Migration from other storage engines, and compatibility with them, is an important area that we are currently working on. This is an area where we especially value community feedback.
The problem with SysBench may be related to the fact that we do not yet support pessimistic locking. We will investigate this further to see if that is the case. We will of course support SysBench and other benchmarks.
As we ramp up our processes, we expect to encounter some hiccups, but we are dedicated to producing a quality product, with the participation of our community. We appreciate your feedback, and thanks again for all your help.
Jonathan Cheyer
Open Source Community Manager
Solid Information Technology
Jonathan,
Thank you for response.
One additional from me – In the latest bug with Sysbench I used optimistic (default) locking.
Vadim: thanks, I’ll pass that info along to our engineering folks.
Vadim, you wrote:
PESSIMISTIC mode, Test 3 Phantom rows:
…
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,
Actually ANSI/ISO Standard for SQL allows phantom rows phenomena at REPEATABLE READ isolation level.
It is not allowed only at the highest isolation level, which is SERIALIZABLE.
Was it a mistake in your text ? If so, I think it is better to correct the text.
Other notions are more or less consistent.
Ilya,
This is right. I specially mention earlier in the article I use “REPEATABLE READ” in Innodb sense not in ANSI Sense. This Will look like double bug to most MySQL/Innodb users.
Most applications written for MySQL/Innodb rely (often unintentially) on this behavior.
On the side note I personally think in this case standard is flawed, probably done to accommodate vendors who could not implement repeatable read to be simply repeatable read without any exceptions. It is easy to explain easy to use.
Just to note – even some MySQL tools rely on it. Ie mysqldump –single-transaction is used to dump transactional tables in consistent snapshot using repeatable-read isolation. This obviously would not work with phantoms.
Thanks, very nice post. It helps me a lot.