myth/db/isolation.go

// https://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels

// Isolation levels
// Of the four ACID properties in a DBMS (Database Management System), the // isolation property is the one most often relaxed. When attempting to maintain the highest level of isolation, a DBMS usually acquires locks on data which may result in a loss of concurrency, or implements multiversion concurrency control. This requires adding logic for the application to function correctly.
// Most DBMSs offer a number of transaction isolation levels, which control the degree of locking that occurs when selecting data. For many database applications, the majority of database transactions can be constructed to avoid requiring high isolation levels (e.g. SERIALIZABLE level), thus reducing the locking overhead for the system. The programmer must carefully analyze database access code to ensure that any relaxation of isolation does not cause software bugs that are difficult to find. Conversely, if higher isolation levels are used, the possibility of deadlock is increased, which also requires careful analysis and programming techniques to avoid.
// Since each isolation level is stronger than those below, in that no higher isolation level allows an action forbidden by a lower one, the standard permits a DBMS to run a transaction at an isolation level stronger than that requested (e.g., a "Read committed" transaction may actually be performed at a "Repeatable read" isolation level).
// The isolation levels defined by the ANSI/ISO SQL standard are listed as follows.

// Serializable
// This is the highest isolation level.
// With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon.
// When using non-lock based concurrency control, no locks are acquired; however, if the system detects a write collision among several concurrent transactions, only one of them is allowed to commit. See snapshot isolation for more details on this topic.
// From : (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992: The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

// Repeatable reads
// In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.
// Write skew is possible at this isolation level in some systems. Write skew is a phenomenon where two writes are allowed to the same column(s) in a table by two different writers (who have previously read the columns they are updating), resulting in the column having data that is a mix of the two transactions.[3][4]

// Read committed
// In this isolation level, a lock-based concurrency control DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed (so the non-repeatable reads phenomenon can occur in this isolation level). As in the previous level, range-locks are not managed.
// Putting it in simpler words, read committed is an isolation level that guarantees that any data read is committed at the moment it is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It makes no promise whatsoever that if the transaction re-issues the read, it will find the same data; data is free to change after it is read.

// Read uncommitted
// This is the lowest isolation level. In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.

package db

import "database/sql"

// IsolationDefault
func IsolationDefault(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelDefault}
}

// IsolationReadUncommitted
func IsolationReadUncommitted(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelReadUncommitted}
}

// IsolationReadCommitted
func IsolationReadCommitted(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelReadCommitted}
}

// IsolationWriteCommitted
func IsolationWriteCommitted(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelWriteCommitted}
}

// IsolationRepeatableRead
func IsolationRepeatableRead(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelRepeatableRead}
}

// IsolationSnapshot
func IsolationSnapshot(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelSnapshot}
}

// IsolationSerializable
func IsolationSerializable(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelSerializable}
}

// IsolationLinearizable
func IsolationLinearizable(readOnly bool) *sql.TxOptions {
	return &sql.TxOptions{ReadOnly: readOnly, Isolation: sql.LevelLinearizable}
}