Perilaku mesin di sini adalah data, bukan kode: setiap paket menyatakan, per level, apa yang dilihat sebuah pembacaan, apa yang dilakukan sebuah penulisan ketika kalah bersaing, lock apa yang diambil setiap operasi dan sampai kapan, serta apakah ada serialization check. Setiap aturan membawa kutipan verbatim dari dokumentasi vendornya, dan build gagal tanpa itu.
Isolation Anomalypostgres-16MVCC throughout. Readers never block writers. REPEATABLE READ is snapshot isolation and permits write skew; READ UNCOMMITTED is an alias for READ COMMITTED; SERIALIZABLE adds Serializable Snapshot Isolation on top of REPEATABLE READ and aborts rather than blocks.
Dokumentasi dibaca pada 2026-08-06 · Level bawaan READ COMMITTED · Baca dokumentasinya
READ UNCOMMITTED (behaves as READ COMMITTED)
PostgreSQL accepts the level name and gives you READ COMMITTED. Dirty reads are not possible in PostgreSQL at any level — the alias is the lesson, not a limitation of this model.
“In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed.”
READ COMMITTED
The default. Every statement takes a fresh snapshot, so two reads in one transaction can disagree. A write to a row a concurrent transaction has just committed is re-applied to the new version rather than aborted.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed by concurrent transactions during the query's execution. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.”
plainRead: record none, gap none, held for statement
“The main advantage of using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.”
lockingRead: record exclusive, gap none, held for transaction
“FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.”
write: record exclusive, gap none, held for transaction
“Row-level locks are released at transaction end or during savepoint rollback, just like table-level locks.”
insert: record exclusive, gap none, held for transaction
“Row-level locks do not affect data querying; they block only writers and lockers to the same row.”
none
“In fact, this isolation level works exactly the same as Repeatable Read except that it also monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions.”
REPEATABLE READ (Snapshot Isolation)
Snapshot isolation, not the ANSI level of the same name. One snapshot for the whole transaction, so phantoms are prevented — more than ANSI requires — while write skew is permitted, which ANSI never mentions. A write to a row committed by a concurrent transaction aborts with 40001.
snapshot: transaction · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed by concurrent transactions during the transaction's execution.”
writeOnStaleRow: abort · lockingReadOnStaleRow: abort · writeWriteBlocks: true
“In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update”
plainRead: record none, gap none, held for statement
“This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.”
lockingRead: record exclusive, gap none, held for transaction
“FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.”
write: record exclusive, gap none, held for transaction
“Row-level locks are released at transaction end or during savepoint rollback, just like table-level locks.”
insert: record exclusive, gap none, held for transaction
“The table also shows that PostgreSQL's Repeatable Read implementation does not allow phantom reads.”
none
“This is a stronger guarantee than is required by the SQL standard for this isolation level, and prevents all of the phenomena described in Table 13.1 except for serialization anomalies.”
SNAPSHOT (no such level name)
PostgreSQL accepts only the four standard level names, so there is nothing to select here. What other engines call SNAPSHOT is what PostgreSQL calls REPEATABLE READ — select that instead.
“The Repeatable Read isolation level is implemented using a technique known in academic database literature and in some other database products as Snapshot Isolation.”
SERIALIZABLE (Serializable Snapshot Isolation)
REPEATABLE READ plus monitoring of read/write dependencies. Write skew is caught and one transaction is aborted with 40001 — no extra blocking, so the cost is retries rather than waits.
snapshot: transaction · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“In fact, this isolation level works exactly the same as Repeatable Read except that it also monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions.”
writeOnStaleRow: abort · lockingReadOnStaleRow: abort · writeWriteBlocks: true
“But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update”
plainRead: record none, gap none, held for statement
“This monitoring does not introduce any blocking beyond that present in repeatable read, but there is some overhead to the monitoring, and detection of the conditions which could cause a serialization anomaly will trigger a serialization failure.”
lockingRead: record exclusive, gap none, held for transaction
“FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.”
write: record exclusive, gap none, held for transaction
“Row-level locks are released at transaction end or during savepoint rollback, just like table-level locks.”
insert: record exclusive, gap none, held for transaction
“To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first.”
ssi
“This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently.”
serializationFailure: 40001 — could not serialize access due to concurrent update
“ERROR: could not serialize access due to concurrent update”
readWriteDependencies: 40001 — could not serialize access due to read/write dependencies among transactions
“ERROR: could not serialize access due to read/write dependencies among transactions”
deadlock: 40P01 — deadlock detected
“PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete.”
abortedTransaction: 25P02 — current transaction is aborted, commands ignored until end of transaction block
“Class 25 — Invalid Transaction State ... 25P02 in_failed_sql_transaction”
Kutipan: 22
mysql-8-innodbDefaults to REPEATABLE READ, not READ COMMITTED, and its REPEATABLE READ is not PostgreSQL's. A consistent read uses the snapshot from the transaction's first read, but a locking read, UPDATE or DELETE acts on the freshest committed row and never aborts for it — so a lost update that PostgreSQL refuses with 40001 succeeds here. Phantoms are prevented for locking reads by next-key locks, and gap locking is switched off entirely at READ COMMITTED. SERIALIZABLE turns every plain SELECT into SELECT ... FOR SHARE, which is why write skew ends in a deadlock rather than a serialization failure. This model breaks deadlocks by rolling back the transaction whose wait closed the cycle; InnoDB documents only that it picks a small transaction, so every schedule here is checked against the running server.
Dokumentasi dibaca pada 2026-08-06 · Level bawaan REPEATABLE READ · Baca dokumentasinya
READ UNCOMMITTED
Genuinely permits dirty reads, unlike PostgreSQL where the same level name is an alias for READ COMMITTED. A read may see a row version written by a transaction that has not committed and may never commit.
snapshot: statement · readsUncommitted: true · lockingReadsSeeLatestCommitted: true
“SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them.”
plainRead: record none, gap none, held for statement
“SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used.”
lockingRead: record exclusive, gap none, held for transaction
“For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.”
write: record exclusive, gap none, held for transaction
“A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.”
insert: record exclusive, gap insertIntention, held for transaction
“An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.”
none
“Otherwise, this isolation level works like READ COMMITTED.”
READ COMMITTED (gap locking disabled)
Every consistent read takes a fresh snapshot, and gap locking is switched off for searches and index scans — so other sessions can insert into the gaps a locking read has looked at, and phantoms become possible again.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“With FOR SHARE, a locking read occurs instead: A SELECT blocks until the transaction containing the freshest rows ends.”
plainRead: record none, gap none, held for statement
“A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions.”
lockingRead: record exclusive, gap none, held for transaction
“For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.”
write: record exclusive, gap none, held for transaction
“Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.”
insert: record exclusive, gap insertIntention, held for transaction
“Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps.”
none
“Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps.”
REPEATABLE READ (the default, with next-key locking)
The default level, and not the same thing as PostgreSQL's. Plain reads share one snapshot taken at the transaction's first read; locking reads and DML act on the freshest committed row instead, and act without complaint — so two transactions can read the same value and both write from it. Range locking reads take next-key locks, which block insertions into the gaps they scanned.
snapshot: transaction · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them.”
plainRead: record none, gap none, held for statement
“Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.”
lockingRead: record exclusive, gap gap, held for transaction
“For other search conditions: InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.”
write: record exclusive, gap none, held for transaction
“For a unique index with a unique search condition: InnoDB locks only the index record found, not the gap before it.”
insert: record exclusive, gap insertIntention, held for transaction
“A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.”
none
“Gap locks in InnoDB are "purely inhibitive", which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist.”
SNAPSHOT (no such level name)
MySQL implements the four standard level names and has no level called SNAPSHOT. What other engines call SNAPSHOT is closest to InnoDB's REPEATABLE READ for plain reads — but not for locking reads or DML, which see the freshest committed row. Select REPEATABLE READ and read that difference.
“InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.”
SERIALIZABLE (plain SELECT becomes SELECT ... FOR SHARE)
REPEATABLE READ with every plain SELECT silently promoted to a locking read. There is no serialization check and nothing is aborted for a read/write dependency: the shared next-key locks simply make the conflicting interleaving impossible, and a schedule that would have been write skew deadlocks instead.
snapshot: transaction · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“With FOR SHARE, a locking read occurs instead: A SELECT blocks until the transaction containing the freshest rows ends.”
plainRead: record shared, gap gap, held for transaction
“This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled.”
lockingRead: record exclusive, gap gap, held for transaction
“For other search conditions: InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.”
write: record exclusive, gap none, held for transaction
“For a unique index with a unique search condition: InnoDB locks only the index record found, not the gap before it.”
insert: record exclusive, gap insertIntention, held for transaction
“An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.”
none
“When deadlock detection is enabled (the default), InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock.”
serializationFailure: 1213 — Deadlock found when trying to get lock; try restarting transaction
“When deadlock detection is enabled (the default), InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock.”
deadlock: 1213 — Deadlock found when trying to get lock; try restarting transaction
“InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.”
Kutipan: 22
sqlserver-2022Lock-based by default, which makes it the odd one out. READ UNCOMMITTED genuinely dirty-reads; READ COMMITTED takes shared locks and releases them as each row is read; REPEATABLE READ holds those shared locks to the end of the transaction, so readers block writers and phantoms remain possible; SERIALIZABLE adds key-range locks and prevents them. SNAPSHOT is the only versioned level here and the only level name of the five that any engine in this project actually implements under that name — it needs ALLOW_SNAPSHOT_ISOLATION ON, and it aborts an update conflict with 3960 rather than blocking. This pack models the default database options, with READ_COMMITTED_SNAPSHOT OFF. Two boundaries are declared rather than guessed: SQL Server picks a deadlock victim by internal cost estimate — the same schedule loses T1 at REPEATABLE READ and T2 at SERIALIZABLE — so a deadlock is refused instead of half-answered; and after the engine rolls a transaction back, the session keeps accepting statements and runs each on its own, which is how a swallowed 3960 still ends up writing to the table.
Dokumentasi dibaca pada 2026-08-06 · Level bawaan READ COMMITTED · Baca dokumentasinya
READ UNCOMMITTED
Real dirty reads, and the reason the level name exists at all. No shared locks are taken and exclusive locks do not block the reader, so a value that is rolled back a moment later can be read and acted on.
snapshot: statement · readsUncommitted: true · lockingReadsSeeLatestCommitted: true
“READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that were modified but not committed by other transactions. When this option is set, it's possible to read uncommitted modifications, which are called dirty reads.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“Choosing a transaction isolation level doesn't affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
plainRead: record none, gap none, held for statement
“Transactions running at the READ UNCOMMITTED level don't issue shared locks to prevent other transactions from modifying data read by the current transaction.”
lockingRead: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the SERIALIZABLE transaction isolation level.”
none
“This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.”
READ COMMITTED (shared locks, not row versioning)
The default. With READ_COMMITTED_SNAPSHOT OFF this is lock-based, not versioned: the reader takes a shared lock and releases it as each row is read, so a read blocks until a writer finishes rather than seeing an older version. Non-repeatable reads and phantoms remain possible between statements.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Specifies that statements can't read data that was modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“Additionally, an update made at the READ COMMITTED isolation level uses update locks on the data rows selected, whereas an update made at the SNAPSHOT isolation level uses row versions to select rows to update.”
plainRead: record shared, gap none, held for statement
“If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it is released. Row locks are released before the next row is processed.”
lockingRead: record exclusive, gap none, held for transaction
“If the transaction modifies a row after it was read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data.”
none
“For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.”
REPEATABLE READ (shared locks held to commit)
The ANSI level, implemented the ANSI way: shared locks on everything read, held until the transaction ends, so nobody can change a row you have read — and phantoms are still permitted, because a range with no rows in it has nothing to lock. This is the one place in the project where REPEATABLE READ means what a textbook says it means.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Specifies that statements can't read data that was modified but not yet committed by other transactions, and that no other transactions can modify data that was read by the current transaction until the current transaction completes.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“For example, if a REPEATABLE READ transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.”
plainRead: record shared, gap none, held for transaction
“Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that were read by the current transaction.”
lockingRead: record exclusive, gap none, held for transaction
“If the transaction modifies a row after it was read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement, it retrieves the new rows, which results in phantom reads.”
none
“Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level.”
SNAPSHOT (row versioning, update conflicts aborted)
The only level in this project that any engine implements under the name SNAPSHOT. One version of the data as of the start of the transaction, no read locks at all, and an update to a row someone else has changed since is aborted with 3960 rather than blocked. It is snapshot isolation, so write skew is permitted — the same anomaly PostgreSQL permits at REPEATABLE READ, under a name that at least admits what it is.
snapshot: transaction · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction.”
writeOnStaleRow: abort · lockingReadOnStaleRow: abort · writeWriteBlocks: true
“If the UPDLOCK hint is used in a write when SNAPSHOT isolation is in use, the transaction must have access to the latest version of the row. If the latest version is no longer visible, it's possible to receive Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict.”
plainRead: record none, gap none, held for statement
“Except when a database is being recovered, SNAPSHOT transactions don't request locks when reading data. SNAPSHOT transactions reading data don't block other transactions from writing data. Transactions writing data don't block SNAPSHOT transactions from reading data.”
lockingRead: record exclusive, gap none, held for transaction
“If the UPDLOCK hint is used in a write when SNAPSHOT isolation is in use, the transaction must have access to the latest version of the row.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap none, held for transaction
“Data modifications made by other transactions after the start of the current transaction aren't visible to statements executing in the current transaction.”
none
“The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level.”
SERIALIZABLE (key-range locks)
Serializability by locking, not by detection: shared locks held to commit, plus key-range locks over the ranges a statement read, so nobody can insert a key into a range you have looked at. Nothing is aborted for a read/write dependency — the interleaving is simply prevented, and the cost is blocking and the occasional deadlock.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Statements can't read data that was modified but not yet committed by other transactions.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“No other transactions can modify data that was read by the current transaction until the current transaction completes.”
plainRead: record shared, gap gap, held for transaction
“Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction.”
lockingRead: record exclusive, gap gap, held for transaction
“Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the SERIALIZABLE transaction isolation level. Key-range locking prevents phantom reads.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.”
none
“This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes.”
serializationFailure: 3960 — Snapshot isolation transaction aborted due to update conflict.
“If the UPDLOCK hint is used in a write when SNAPSHOT isolation is in use, the transaction must have access to the latest version of the row. If the latest version is no longer visible, it's possible to receive Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict.”
deadlock: 1205 — Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
“The Database Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns error 1205 to the application.”
commitAfterAbort: 3902 — The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
“Issuing a COMMIT TRANSACTION when @@TRANCOUNT is zero results in an error because there's no corresponding BEGIN TRANSACTION.”
Kutipan: 31
sqlserver-2022-rcsiThe same SQL Server with one database option flipped: READ_COMMITTED_SNAPSHOT ON. It is a different engine to program against. READ COMMITTED stops taking shared locks and starts handing each statement a versioned snapshot, so readers no longer block on writers and the dirty-read scenario stops waiting — the values it reads are the same, and what changes is who waits for whom. Everything else is untouched: REPEATABLE READ and SERIALIZABLE still lock, SNAPSHOT is still SNAPSHOT, and write skew is still permitted there. Shipped as a separate pack rather than a flag, because a database option that changes what a level means deserves its own citations and its own recordings.
Dokumentasi dibaca pada 2026-08-06 · Level bawaan READ COMMITTED · Baca dokumentasinya
READ UNCOMMITTED
Real dirty reads, and the reason the level name exists at all. No shared locks are taken and exclusive locks do not block the reader, so a value that is rolled back a moment later can be read and acted on.
snapshot: statement · readsUncommitted: true · lockingReadsSeeLatestCommitted: true
“READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that were modified but not committed by other transactions. When this option is set, it's possible to read uncommitted modifications, which are called dirty reads.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“Choosing a transaction isolation level doesn't affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
plainRead: record none, gap none, held for statement
“Transactions running at the READ UNCOMMITTED level don't issue shared locks to prevent other transactions from modifying data read by the current transaction.”
lockingRead: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the SERIALIZABLE transaction isolation level.”
none
“This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.”
READ COMMITTED (row versioning — READ_COMMITTED_SNAPSHOT ON)
Still statement-scoped, but now versioned rather than locked: each statement is handed a transactionally consistent snapshot as of the moment it started, and no locks are taken to protect it. Non-repeatable reads and phantoms remain possible between statements, exactly as before — this option changes the blocking, not the isolation.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks aren't used to protect the data from updates by other transactions.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“Additionally, an update made at the READ COMMITTED isolation level uses update locks on the data rows selected, whereas an update made at the SNAPSHOT isolation level uses row versions to select rows to update.”
plainRead: record none, gap none, held for statement
“If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks aren't used to protect the data from updates by other transactions.”
lockingRead: record exclusive, gap none, held for transaction
“If the transaction modifies a row after it was read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data.”
none
“For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.”
REPEATABLE READ (shared locks held to commit)
The ANSI level, implemented the ANSI way: shared locks on everything read, held until the transaction ends, so nobody can change a row you have read — and phantoms are still permitted, because a range with no rows in it has nothing to lock. This is the one place in the project where REPEATABLE READ means what a textbook says it means.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Specifies that statements can't read data that was modified but not yet committed by other transactions, and that no other transactions can modify data that was read by the current transaction until the current transaction completes.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“For example, if a REPEATABLE READ transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.”
plainRead: record shared, gap none, held for transaction
“Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that were read by the current transaction.”
lockingRead: record exclusive, gap none, held for transaction
“If the transaction modifies a row after it was read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement, it retrieves the new rows, which results in phantom reads.”
none
“Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level.”
SNAPSHOT (row versioning, update conflicts aborted)
The only level in this project that any engine implements under the name SNAPSHOT. One version of the data as of the start of the transaction, no read locks at all, and an update to a row someone else has changed since is aborted with 3960 rather than blocked. It is snapshot isolation, so write skew is permitted — the same anomaly PostgreSQL permits at REPEATABLE READ, under a name that at least admits what it is.
snapshot: transaction · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction.”
writeOnStaleRow: abort · lockingReadOnStaleRow: abort · writeWriteBlocks: true
“If the UPDLOCK hint is used in a write when SNAPSHOT isolation is in use, the transaction must have access to the latest version of the row. If the latest version is no longer visible, it's possible to receive Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict.”
plainRead: record none, gap none, held for statement
“Except when a database is being recovered, SNAPSHOT transactions don't request locks when reading data. SNAPSHOT transactions reading data don't block other transactions from writing data. Transactions writing data don't block SNAPSHOT transactions from reading data.”
lockingRead: record exclusive, gap none, held for transaction
“If the UPDLOCK hint is used in a write when SNAPSHOT isolation is in use, the transaction must have access to the latest version of the row.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap none, held for transaction
“Data modifications made by other transactions after the start of the current transaction aren't visible to statements executing in the current transaction.”
none
“The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level.”
SERIALIZABLE (key-range locks)
Serializability by locking, not by detection: shared locks held to commit, plus key-range locks over the ranges a statement read, so nobody can insert a key into a range you have looked at. Nothing is aborted for a read/write dependency — the interleaving is simply prevented, and the cost is blocking and the occasional deadlock.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“Statements can't read data that was modified but not yet committed by other transactions.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“No other transactions can modify data that was read by the current transaction until the current transaction completes.”
plainRead: record shared, gap gap, held for transaction
“Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction.”
lockingRead: record exclusive, gap gap, held for transaction
“Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the SERIALIZABLE transaction isolation level. Key-range locking prevents phantom reads.”
write: record exclusive, gap none, held for transaction
“A transaction always holds an exclusive lock to perform data modification, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.”
insert: record exclusive, gap insertIntention, held for transaction
“By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.”
none
“This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes.”
serializationFailure: 3960 — Snapshot isolation transaction aborted due to update conflict.
“If the UPDLOCK hint is used in a write when SNAPSHOT isolation is in use, the transaction must have access to the latest version of the row. If the latest version is no longer visible, it's possible to receive Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict.”
deadlock: 1205 — Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
“The Database Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns error 1205 to the application.”
commitAfterAbort: 3902 — The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
“Issuing a COMMIT TRANSACTION when @@TRANCOUNT is zero results in an error because there's no corresponding BEGIN TRANSACTION.”
Kutipan: 30
oracle-23aiOracle has two isolation levels, not five. READ UNCOMMITTED and REPEATABLE READ do not exist — asking for them is refused here rather than mapped to something else — and dirty reads are impossible at any level. What Oracle calls SERIALIZABLE is snapshot isolation: it gives the transaction one point in time, raises ORA-08177 if you write a row someone else committed since, and permits write skew despite the name. That is the sharpest naming problem in the whole project: a developer who chooses SERIALIZABLE here because the word means something in a textbook has not got what the textbook describes. One more thing Oracle alone does: ORA-08177 rolls back the statement and leaves the transaction open, so a COMMIT that follows commits the work done before the failure. Oracle also declines to promise which session loses a deadlock — "either session could get the error" — so a deadlock is refused here rather than guessed, and only the statement would be rolled back in any case.
Dokumentasi dibaca pada 2026-08-06 · Level bawaan READ COMMITTED · Baca dokumentasinya
READ UNCOMMITTED (no such level)
Oracle offers read committed and serializable, and a read-only mode. There is no READ UNCOMMITTED to select, and no level at which a dirty read is possible — so unlike PostgreSQL, which accepts the name and quietly gives you READ COMMITTED, Oracle does not accept it at all.
“Oracle AI Database never permits a dirty read, which occurs when a transaction reads uncommitted data in another transaction.”
READ COMMITTED
The default. Every query sees data committed before that query began, so two reads in one transaction can disagree. A write to a row another transaction has just committed is re-applied to the new version rather than refused.
snapshot: statement · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“In the read committed isolation level, every query executed by a transaction sees only data committed before the query—not the transaction—began.”
writeOnStaleRow: applyToLatest · lockingReadOnStaleRow: readLatest · writeWriteBlocks: true
“Oracle AI Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent for a single point in time.”
plainRead: record none, gap none, held for statement
“Oracle AI Database never permits a dirty read, which occurs when a transaction reads uncommitted data in another transaction.”
lockingRead: record exclusive, gap none, held for transaction
“Oracle AI Database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, releasing one set of the conflicting row locks.”
write: record exclusive, gap none, held for transaction
“The statement rolled back belongs to the transaction that detects the deadlock. Usually, the signaled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.”
insert: record exclusive, gap none, held for transaction
“Oracle AI Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent for a single point in time.”
none
“In the read committed isolation level, every query executed by a transaction sees only data committed before the query—not the transaction—began.”
REPEATABLE READ (no such level)
Oracle has no REPEATABLE READ. What it offers instead is SERIALIZABLE, which gives the whole transaction one point in time — stronger than ANSI's REPEATABLE READ for reads, and still not serializable. Select SERIALIZABLE and read what it actually prevents.
“Oracle AI Database offers the read committed (default) and serializable isolation levels. Also, the database offers a read-only mode.”
SNAPSHOT (the level Oracle has, under another name)
There is no level called SNAPSHOT to select — and yet snapshot isolation is exactly what Oracle gives you when you ask for SERIALIZABLE. This is the one refusal in the project that points at a level the engine really does implement, wearing a different name.
“A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database.”
SERIALIZABLE (Snapshot Isolation)
Snapshot isolation under the strongest name in the standard. One point in time for the whole transaction, ORA-08177 if you write a row committed since it began — and write skew permitted, because two transactions writing different rows never collide. There is no serialization check to catch it, so the on-call roster empties here exactly as it does at PostgreSQL's REPEATABLE READ.
snapshot: transaction · readsUncommitted: false · lockingReadsSeeLatestCommitted: true
“In the serializable isolation level, a transaction sees only changes committed at the time the transaction—not the query—began and changes made by the transaction itself.”
writeOnStaleRow: abort · lockingReadOnStaleRow: abort · writeWriteBlocks: true
“The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began.”
plainRead: record none, gap none, held for statement
“Oracle AI Database can also provide read consistency to all queries in a transaction, known as transaction-level read consistency. In this case, each statement in a transaction sees data from the same point in time. This is the time at which the transaction began.”
lockingRead: record exclusive, gap none, held for transaction
“Oracle AI Database permits a serializable transaction to modify a row only if changes to the row made by other transactions were already committed when the serializable transaction began.”
write: record exclusive, gap none, held for transaction
“The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began.”
insert: record exclusive, gap none, held for transaction
“A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database.”
none
“A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database.”
serializationFailure: 8177 — ORA-08177: can't serialize access for this transaction
“Oracle AI Database permits a serializable transaction to modify a row only if changes to the row made by other transactions were already committed when the serializable transaction began. The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began.”
deadlock: 60 — ORA-00060: deadlock detected while waiting for resource
“Oracle AI Database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, releasing one set of the conflicting row locks.”
Kutipan: 12