A transaction is a logical unit of work. A minimum unit is defined by work process, not a system or a technical thing. These transactions have an ACID (Atomicity, Consistency, Isolation, Durability) characteristic. Isolation is to prevent other transactions from approaching while data are being processed by some transactions. DBMS provides a "Lock" function to preserve data that can be distorted by parallel transactions as a concurrency issue. Lock simply means that only one user or session can be modified for a particular operator. You have to strike the balance between consistency and performance.
Below are the contents about 'Shared & Exclusive Lock' on mysql manual
- A shared (
S) lock permits the transaction that holds the lock to read a row.
- An exclusive (
X) lock permits the transaction that holds the lock to update or delete a row.
T1holds a shared (
S) lock on row
r, then requests from some distinct transaction
T2for a lock on row
rare handled as follows:
- A request by
Slock can be granted immediately. As a result, both
- A request by
Xlock cannot be granted immediately.
If a transaction
T1holds an exclusive (
X) lock on row
r, a request from some distinct transaction
T2for a lock of either type on
rcannot be granted immediately. Instead, transaction
T2has to wait for transaction
T1to release its lock on row
In summary, An exclusive lock does not permit other two types of transactions lock And it even waits for other shared transaction lock before acquiring locks. And shared lock can be permitted among the shared lock.
Below are the contents about 'Isolation level' on mysql manual
You can enforce a high degree of consistency with the default
REPEATABLE READlevel, for operations on crucial data where ACID compliance is important. Or you can relax the consistency rules with
READ COMMITTEDor even
READ UNCOMMITTED, in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking.
SERIALIZABLEenforces even stricter rules than
REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.
The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used.
This is the default isolation level for
InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking)
SELECTstatements within the same transaction, these
SELECTstatements are consistent also with respect to each other. See Section 184.108.40.206, “Consistent Nonlocking Reads”.
For locking reads (
DELETEstatements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.
- For a unique index with a unique search condition,
InnoDBlocks only the index record found, not the gap before it.
- For other search conditions,
InnoDBlocks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.
Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, see Section 220.127.116.11, “Consistent Nonlocking Reads”.
For locking reads (
InnoDBlocks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.
Because gap locking is disabled, phantom problems may occur, as other sessions can insert new rows into the gaps. For information about phantoms, see Section 15.7.4, “Phantom Rows”.
Only row-based binary logging is supported with the
READ COMMITTEDisolation level. If you use
binlog_format=MIXED, the server automatically uses row-based logging.
READ COMMITTEDhas additional effects:
InnoDBholds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the
WHEREcondition. This greatly reduces the probability of deadlocks, but they can still happen.
UPDATEstatements, if a row is already locked,
InnoDBperforms a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the
WHEREcondition of the
UPDATE. If the row matches (must be updated), MySQL reads the row again and this time
InnoDBeither locks it or waits for a lock on it.
Consider the following example, beginning with this table:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT;
In this case, the table has no indexes, so searches and index scans use the hidden clustered index for record locking (see Section 18.104.22.168, “Clustered and Secondary Indexes”) rather than indexed columns.
Suppose that one session performs an
UPDATEusing these statements:
# Session A START TRANSACTION; UPDATE t SET b = 5 WHERE b = 3;
Suppose also that a second session performs an
UPDATEby executing these statements following those of the first session:
# Session B UPDATE t SET b = 4 WHERE b = 2;
UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If
InnoDBdoes not modify the row, it releases the lock. Otherwise,
InnoDBretains the lock until the end of the transaction. This affects transaction processing as follows.
When using the default
REPEATABLE READisolation level, the first
UPDATEacquires an x-lock on each row that it reads and does not release any of them:
x-lock(1,2); retain x-lock x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); retain x-lock x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); retain x-lock
x-lock(1,2); block and wait for first UPDATE to commit or roll back
READ COMMITTEDis used instead, the first
UPDATEacquires an x-lock on each row that it reads and releases those for rows that it does not modify:
x-lock(1,2); unlock(1,2) x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); unlock(3,2) x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); unlock(5,2)
For the second
InnoDBdoes a “semi-consistent” read, returning the latest committed version of each row that it reads to MySQL so that MySQL can determine whether the row matches the
WHEREcondition of the
x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); retain x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); retain x-lock
However, if the
WHEREcondition includes an indexed column, and
InnoDBuses the index, only the indexed column is considered when taking and retaining record locks. In the following example, the first
UPDATEtakes and retains an x-lock on each row where b = 2. The second
UPDATEblocks when it tries to acquire x-locks on the same records, as it also uses the index defined on column b.
CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB; INSERT INTO t VALUES (1,2,3),(2,2,4); COMMIT; # Session A START TRANSACTION; UPDATE t SET b = 3 WHERE b = 2 AND c = 3; # Session B UPDATE t SET b = 4 WHERE b = 2 AND c = 4;
The effects of using the
READ COMMITTEDisolation level are the same as enabling the deprecated
innodb_locks_unsafe_for_binlogconfiguration option, with these exceptions:
innodb_locks_unsafe_for_binlogis a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.
innodb_locks_unsafe_for_binlogcan be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.
READ COMMITTEDtherefore offers finer and more flexible control than
SELECTstatements 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
This level is like
REPEATABLE READ, but
InnoDBimplicitly converts all plain
SELECT ... FOR SHAREif
autocommitis disabled. If
autocommitis enabled, the
SELECTis its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain
SELECTto block if other transactions have modified the selected rows, disable