What is the relationship between transactions at different levels of isolation?

for example, the isolation level of transaction An is repeatable read
my understanding is that a read sql statement of transaction An is the same as the result repeated many times,
regardless of whether or not other transactions have changed the data rows / tables affected by sql

and if the isolation level of transaction An is read committed
, the rows / tables affected by transaction A can only be seen by other transactions after the transaction commits.

so these two isolation levels, one is the influence of other people"s actions on their own reading
, the other is the influence of their own actions on others" reading
feeling is not consistent
because I think it is either the influence of other people"s actions on their own reading
or the influence of their own actions on others" reading

how to understand?

and serializable is even more difficult to understand
if a transaction is serializable, which means that the transaction monopolizes its affected data tables / rows during execution, and operations in any other isolation-level transaction cannot be performed? Or will both transactions affect each other at the serializable isolation level?

Feb.26,2021

can the same database have different isolation levels?


transaction isolation level:

  • uncommitted read ( READ UNCOMMITED ): two transactions are visible to each other, and data can be obtained even if another transaction is not committed ( not recommended ). this is called dirty reading ;
  • committed read ( READ COMMITED ): according to the basic concept of isolation, when a transaction is in progress, other committed things are visible to that transaction, that is, you can get the data committed by other transactions.
  • repeatable ( REPEATABLE READ ): default isolation level of InnoDB . When a transaction is in progress, all other transactions are not visible to it, that is, multiple reads are performed, and the result is the same!
  • serializable ( SERIALIZABLE ): locks are added on every row of data read, resulting in a large number of lock timeouts and lock requisitions, strict data consistency and no concurrency are available.

clipboard.png

View the transaction isolation level of the system: show variables like'% iso%' ;
start a new transaction: begin ;
commit a transaction: commit ;
modify the isolation level of things: set session tx_isolation='read-committed' ;


transaction isolation level affects the interior of the transaction. Just manage your own lock. It has nothing to do with other transactions, and there is no conflict.
read is unlocked, rc is the data that can be read will not change, rr reads snapshot data will not change, and serial transactions are queued up.
write is to add their own locks. No matter what isolation level, locks cannot be updated. If you add a lock without a lock, what is the impact on each other?

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1bdb83a-3126e.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1bdb83a-3126e.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?