CODEWITHSUNDEEP
mysqlisolation-level
Alberto
Alberto

Reputation: 359

MySQL. What isolation level should be used?

There are many situations where I don't know what kind of isolation I should use in MySQL to avoid using corrupt data.

For example, let's say an application to make reservations for a theater. A user selects a seat and, within a transaction, it is checked that the selected seat is free and, if it is, the reservation is saved in a second step.

It may happen that while is verified that the seat is free and, before saving the reservation, another user reserve it first.

For situations like this and working with MySQL, what isolation level should I use?

Upvotes: 2

Views: 832

Answers (2)

Harshit Gangwar
Harshit Gangwar

Reputation: 553

If you looking for how to check Isolation level your DB is using currently, you can use the following query.

Note that Default Isolation level in MySQL is REPEATABLE READ

SHOW VARIABLES LIKE 'transaction_isolation';

Example showing the use of same query

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

Shadow's right about the need for application level support for this kind of operation. In a web app in particular, each page view or AJAX request is a separate operation and may use its own connection, so you can't hold a transaction in an uncommitted state for multiple pageviews.

That being said, the default REPEATABLE READ isolation level should serve you well if your transactions do SELECT ... FOR UPDATE ; UPDATE ; COMMIT sequences.

Upvotes: 3

Related Questions