Understanding MySQL Transaction Isolation Levels by Example

Photo by Georg Bommeli on Unsplash

Most database systems use Read Committed is the default isolation level (MySQL using Repeatable Read instead).

Choosing the isolation level is about finding the right balance of consistency and scalability for our current application requirements.

In this post, we will deep into the MySQL Transaction Isolation Levels.

Preparation

Let’s first create a fake user table

CREATE TABLE `users` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `username` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT 'password01',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Then, add some data records.

INSERT INTO `users` (`id`, `username`, `password`)
VALUES
 (1,'jerry','LoveCats01'),
 (2, 'tom', 'wysiwyg77'),
 (3,'herry','password01'),
 (4, 'lily','password02');

Sample User Table Record

Now, it’s time to unveil the mysteries of the transaction isolations levels.