SQL语句和事务的区别

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own.

read uncommited, read commited, repeatable read, serializeable(顺序)

• 一致性保护级别由低到高
• 锁什么时候释放
  • In REPEATABLE READ every lock acquired during a transaction is held for the duration of the transaction.
  • In READ COMMITTED the locks that did not match the scan are released after the STATEMENT completes

• read uncommmited 会导致脏读，事务A读取了个事务B还没commit的数据，如果此时事务B rollback，那么事务A读取到的数据就是不对的。怎么解决这个问题？

• read commmited 事务A可以读取事务B提交后的数据，会导致幻读，也就是事务A多次读取到的数据不一致(insert or update)，怎么解决这个问题？

  • For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), 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.
  • Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.(MVCC)

• repeatable read
  • repeatable是什么意思？同一事务多次读到的数据一致
  • For consistent reads
    • A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.
    • All consistent reads within the same transaction read the snapshot established by the first read. ( read commmited级别，读到的是fresh snapshot)
  • For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements
    • unique index with a unique search: lock only index record
    • other search conditions: locks the index range scanned, using gap locks or next-key locks