isolation_levels.txt (2416B)
Q: In MariaDB, explain what transaction isolation levels are and how they affect concurrency. Can you name the four standard isolation levels and briefly describe the differences between them? A: In InnoDB, each transaction gets a snapshot of the data at the moment it starts (technically, when it runs its first consistent read). After that, the transaction is reading historical versions of rows from that snapshot, not the latest committed data in real time. MariaDB supports the 4 different transaction isolation levels. * -> READ UNCOMMITTED A transaction may see not-yet-committed changes made by other transactions. This isolation level is good when making rough estimates(like COUNT(*) or SUM(*)) etc. * -> READ COMMITTED Under this isolation level, each statement gets a fresh snapshot at the time it begins. So if you issue two SELECTs one after another, the second sees the latest committed data right away, even if it’s only microseconds newer. * -> REPEATABLE READ Your first read defines the snapshot for the entire transaction. You’ll keep seeing that version until you COMMIT or ROLLBACK, regardless of how much real-world time passes. REPEATABLE READ is the default isolation level for MySQL and MariaDB. * -> SERIALIZABLE Same snapshot logic as REPEATABLE READ, but with extra locking so other transactions can’t interfere logically. Useful observations: When autocommit=1, every statement implicitly starts and ends its own transaction, both REPEATABLE READ and READ COMMITTED behave identically for single statements, and they both see the latest committed data before execution. So with autocommit enabled, you’re not running multi-statement transactions at all, and each SELECT, UPDATE, or INSERT is its own short-lived, fully committed transaction. Stored procedures are one of the few everyday cases where autocommit=1 doesn’t shield you from transactional differences, because the procedure itself can span multiple statements inside a single transaction. Here’s what actually happens: Even with autocommit=1 globally, when you execute a stored procedure, it runs as one logical transaction unless you explicitly COMMIT or ROLLBACK inside it. So if that procedure does a few INSERTs, UPDATEs, and SELECTs, those statements all share the same transactional context meaning isolation level difference between READ COMMITTED and REPEATABLE READ matters again.