“This is my first day to participate in the Gwen Challenge.
ACID (Atomicity, Consistency, Isolation, Durability)
I won’t go into the conceptual stuff, but I’ve recently encountered some issues with transaction isolation.
Briefly review several levels of transaction isolation features
Read UNcommitted, read COMMITTED, REPEATable Read, Serializable
Select @@transaction_ISOLATION;
Reference: dev.mysql.com/doc/refman/…
Briefly discuss a few issues regarding isolation features
Question 1: What does repeatable mean?
The same query statement in the same transaction returns the same result. Not affected by other transactions.
Question 2 If an update or insert operation occurs on amount in the same transaction, is the result of the update or insert query returned before the transaction is committed? Why is that? Answer: The update or insert results are read. This is because select read data has two modes, if the unlocked mode is snapshot read, if add lock in share mode (for update) for current read.
Question 3. How do snapshot reads and current reads solve the illusion problem at the repeat read level respectively in a transaction?
MVCC and next – the key lock
Question 4. If neither transaction commits, how does the order of insert statements executed in each transaction affect the results in the final table? Why is that?
The table is recorded in the order in which the inserts are performed. For UPDATE, DELETE, and INSERT statements, InnoDB automatically assigns an exclusive lock (X) to the data set involved.
If you don’t already understand this, the next article will give you an insight into MYSQL locks.