Description of the default transaction isolation level of mysql and oracle

1. Transaction characteristics (ACID)

(1) Atomicity. The database modification operations performed by the programs involved in the transaction either all succeed or all fail.

(2) Consistency The source and destination are balanced before and after the transaction is executed.

(3) Isolation During concurrency, each transaction is isolated and does not affect each other.

(4) Durability Once a transaction is successfully committed, the integrity of the data should be guaranteed.

2. Transaction Isolation Level

(1) read uncommitted

All transactions can see the data of uncommitted transactions.

(2) read committed

The transaction can only be queried after it is successfully submitted.

(3) Repeatable

When multiple instances of the same transaction read data, uncommitted records may be queried, resulting in phantom reads. mysql default level

(4) Serializable

Force sorting and add shared locks on each read data row. This will result in a lot of timeouts and lock contention.

MySQL

The default transaction level of MySQL is 'REPEATABLE-READ', which means repeatable read

1. View the current session isolation level

select @@tx_isolation;

2. View the current isolation level of the system

select @@global.tx_isolation;

3. Set the current session isolation level

set session transaction isolatin level repeatable read;

4. Set the current isolation level of the system

set global transaction isolation level repeatable read;

Oracle

Oracle database supports two transaction isolation levels: READ COMMITTED and SERIALIZABLE.

The default system transaction isolation level is READ COMMITTED, which means read committed

1. Check the system default transaction isolation level, which is also the current session isolation level

--First create a transaction

declare
 trans_id Varchar2(100);
 begin
 trans_id := dbms_transaction.local_transaction_id( TRUE );
 end;

-- Check the transaction isolation level

SELECT s.sid, s.serial#,

  CASE BITAND(t.flag, POWER(2, 28))
    WHEN 0 THEN 'READ COMMITTED'
    ELSE 'SERIALIZABLE'
  END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

Supplement: SQLserver locks and transaction isolation levels

Isolation Level

Lock

Shared lock: For shared read (select), if there is a transaction (one or more) with a shared lock on the data in the table (the amount of locked data depends on the granularity of the lock), the locked data is not allowed to be updated (update)

Exclusive lock: There can only be one, and other transactions cannot obtain shared locks and exclusive locks on the locked data (that is, exclusive locks are incompatible with shared locks. For more information, please see lock compatibility). Here we especially emphasize the locked data.

Locks and Isolation Levels

Type 1

① READUNCOMMITTED: No lock is issued

② READCOMMITTED: Issue a shared lock and hold it until the reading is completed

③ REPEATABLEREAD: Issue a shared lock and hold it until the end of the transaction

④ SERIALIZABLE: Issues a shared lock and holds it until the end of the transaction

Type 2

① NOLOCK: No lock is issued. Equivalent to READUNCOMMITTED

② HOLDLOCK: Issue a shared lock and hold it until the end of the transaction. Equivalent to SERIALIZABLE

③ XLOCK: Issues an exclusive lock and holds it until the transaction ends.

④ UPDLOCK: Issues an update lock and holds it until the transaction ends. (Update lock: does not block other things, allowing other things to read data (that is, update lock is compatible with shared lock), but it ensures that the data has not been updated since the last time it was read)

⑤ READPAST: Issues a shared lock, but skips locking the row, it will not be blocked. Applicable conditions: Commit read isolation level, row-level lock, select statement.

Type 3

① ROWLOCK: row-level lock

② PAGLOCK: Page-level lock

③ TABLOCK: table lock

④ TABLOCKX: table exclusive lock

Using XLOCK in a SELECT statement does not prevent reads. This is because SQL SERVER has a special optimization at the read committed isolation level, which checks whether the row has been modified and ignores XLOCK if it has not been modified. Because this is indeed acceptable in the Read Committed isolation level.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.