Database isolation level

Read Uncommitted

  • Definition: the lowest transaction level that I can read in my own transaction.
  • Disadvantages: May cause dirty read, read data not committed by other transactions;
  • Example:
import records
from threading import Thread
import time


def func1() :
    with db.transaction() as tx:
        tx.query("set session transaction isolation level read uncommitted")
        data = tx.query("select * from keywords where id=1 ")
        print(data.all())
        time.sleep(1)
        data = tx.query("select * from keywords where id=1 ")
        print(data.all())


def func2() :
    with db.transaction() as tx:
        tx.query("set session transaction isolation level read uncommitted")
        tx.query("update keywords set keyword='1' where id=1 ")
        tx.query("update keywords set keyword='2' where id=1 ")
        tx.query("update keywords set keyword='3' where id=1 ")


if __name__ == '__main__':
    db = records.Database("mysql+pymysql://user:password@localhost/weibo? charset=utf8")

    t1 = Thread(target=func2)
    t2 = Thread(target=func1)

    t1.start()
    t2.start()

Copy the code

Results: It may be different every time

[<Record {"id": 1."keyword": "1"."enable": 1}>]
[<Record {"id": 1."keyword": "3"."enable": 1} >]Copy the code

Read Committed

  • Definition:

Someone else’s transaction to change data has been committed and I can read it in my transaction.

  • Disadvantages:

This may result in unrepeatable reads, where two reads of the same thing may be different.

  • The database

Oracle default isolation level

Repeatable Read

  • Definition:

Other people change the data of the transaction has been committed, I do not read in my transaction.

  • Disadvantages:

It is possible that the data is not read in the phantom transaction, but already exists in the database

  • data

Mysql default isolation level

What is the concept of concurrent Version Control (MCVV) and how is it implemented?

Disadvantages of using long transactions? Why can use constant transactions drag down the entire library?

How can a transaction be started?

What is the syntax for commit work and chain?

How to query long transactions in each table?

How to avoid long transactions?