Transaction Isolation Levels in MySQL

Q

What Are Transaction Isolation Levels in MySQL?

✍: FYIcenter.com

A

There are 4 transaction isolation levels defined by SQL-1992 standard:

  • READ UNCOMMITTED - The SELECT statements in one transaction will read uncommitted data changes from transactions of all connected sessions. In this level, "dirty read" could happen, where transaction A reads a record of data including changes made by transaction B, but not committed yet.
  • READ COMMITTED - The SELECT statements in one transaction will read only committed data changes from other transactions, and uncommitted data changes from itself. In this level, "dirty read" will not happen. But "non-repeatable read" could happen, where transaction A executes the same query twice and gets two different outputs, because transaction B committed some data changes between the two executions.
  • REPEATABLE READ - Extending the READ COMMITTED level to ensure that same SELECT statement will get the same data on the same records if executed multiple times. Database server will use locks or data snapshots to make this happen. In this level, "dirty read" and "non-repeatable read" will not happen. But "phantom records" could happen, there transaction A executes the same query twice and gets extra records the second time, because transaction B inserted some new records between the two executions.
  • SERIALIZABLE - Extending the REPEATABLE READ level to ensure that "phantom records" will not happen by using locks or data snapshots.

By default, MySQL server offers the REPEATABLE READ level on tables with transaction-safe storage engines, like InnoDB.

 

Transaction Management: Commit or Rollback in MySQL

⇒⇒MySQL Database Tutorials

2017-08-03, 250👍, 0💬