MySQL Bugs: #100065: Counter-intuitive performance of configuration "transaction

Bug #100065	Counter-intuitive performance of configuration "transaction_isolation"
Submitted:	1 Jul 2020 13:50	Modified:	2 Jul 2020 14:18
Reporter:	haochen he	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Ubuntu (Generic Linux Release)
Assigned to:		CPU Architecture:	Any

Description:
transaction_isolation has 4 values, and from highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

Constraining database access operation execution typically means reduced performance. So it is intuitive that "transaction_isolation = READ-UNCOMMITTED" should has the best performance.

However, what I observe is just opposite: I run main.innodb_deadlock.test for 100 times repeatedly upon the 4 values of transaction_isolation. And I get the averaged execution time of the test case is READ-UNCOMMITTED:2040ms READ-COMMITTED:1881ms REPEATABLE-READ:1910ms SERIALIZABLE:1459ms.

How to repeat:
./mysql-test-run.pl \
--debug-server \
--clean-vardir \
--force-restart \
--report-times \
--max-connections=16 \
--testcase-timeout=60 \
--combination=--transaction-isolation=SERIALIZABLE \
--combination=--transaction-isolation=REPEATABLE-READ \
--combination=--transaction-isolation=READ-COMMITTED \
--combination=--transaction-isolation=READ-UNCOMMITTED \
--repeat=100 \
main.innodb_deadlock

Test result of mysql-test-run.pl

Attachment: test_results.txt (text/plain), 35.18 KiB.

visualized results

Attachment: visulized_results.png (image/png, text), 313.66 KiB.

Hi Mr. he,

Thank you for your bug report.

However, this is not a bug. Isolation levels are NOT ordered by performance, but by protection level and impact  of transactions in each isolation level, on concurrent transactions.

This is fully defined when MVCC was established.

Not a bug.

Thanks for the quick reply.

>>> However, this is not a bug. Isolation levels are NOT ordered by performance, but by protection level and impact of transactions in each isolation level, on concurrent transactions.

I agree that Isolation levels is orderd by isolation protection level for transactions, but given more protections, the performance are expected to be better. Oppositely, less protection results in better performance. 

[1] https://logicalread.com/optimize-mysql-perf-part-2-mc13/#.XvrhypMzZhE
[2] https://en.wikipedia.org/wiki/Isolation_(database_systems)

What I observe is that relaxed isolation level result in worse performance. That is uncommon.

Thanks.

Hi,

Protection level and performance are unrelated.

Not a bug.

For further questions, please read the next comment.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.