Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL Tutorial - Transaction Isolation Levels

By: FYIcenter.com

(Continued from previous topic...)

What Are Transaction Isolation Levels?

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.

(Continued on next topic...)

  1. What Is a Transaction?
  2. How To Start a New Transaction?
  3. How To End the Current Transaction?
  4. How To Create a Table for Transaction Testing?
  5. How To Switch between Autocommit-On and Autocommit-Off Modes?
  6. How To Find Out the Current Transaction Mode?
  7. How To Start a New Transaction Explicitly?
  8. How To Commit the Current Transaction?
  9. How To Rollback the Current Transaction?
  10. What Happens to the Current Transaction If a START TRANSACTION Is Executed?
  11. What Happens to the Current Transaction If a DDL Statement Is Executed?
  12. What Happens to the Current Transaction If the Session Is Ended?
  13. What Happens to the Current Transaction If the Session Is Killed?
  14. How Does MySQL Handle Read Consistency?
  15. What Are Transaction Isolation Levels?
  16. How To View and Change the Current Transaction Isolation Level?
  17. What Is a Data Lock?
  18. How To Experiment Data Locks?
  19. How Long a Transaction Will Wait for a Data Lock?
  20. What Happens to Your Transactions When ERROR 1205 Occurred?
  21. What Is a Dead Lock?
  22. How To Experiment Dead Locks?
  23. What Happens to Your Transactions When ERROR 1213 Occurred?
  24. What Are Impacts on Applications from Locks, Timeouts, and DeadLocks?

MySQL Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...