Summary of transactions and locks

Transactions are used to ensure data consistency (integrity), and locks are used to ensure data concurrency (control the concurrency of multiple transactions) (concurrency ). The higher the lock level, the database concurrency may be affected. The lower the lock level, the better the database concurrency. The usage of locks in JDBC, JTA, and container management transactions is basically the same, and there are also write differences, mainly in the JTA distributed transactions individually and differently. Transactions are only used to ensure data consistency. Consistency includes: whether the transaction can be isolated from other transactions, and whether repeated reads, dirty reads, and dirty writes can be ensured in the same transaction. Oracle automatically schedules the lock if it is not manually locked. The execution sequence of Oracle automatic scheduling locks is normal. Some databases automatically schedule database locks, which increases the possibility of deadlocks. Oracle will never automatically schedule locks. When deadlocks occur, Oracle will also perform automatic scheduling, this will delay for a period of time. Oracle's own policy will roll back a transaction to ensure that all two transactions with deadlocks are executed. There are many types of locks, including DML, DDL locks, and others. We usually use DML and DDL locks during development. DML locks are divided into many aspects:
RS: Row share RX: Row exclusive S: Share SRX: Share row exclusive X: exclusive
(1) RS can use the following declaration method to lock the table's row Select... fromTable... For update ...;

Lock tableTableIn row share mode;

Once the table is locked by RS, it is not allowed to lock the table.TableIn exclusive mode

(2) RX can use the following declaration method to lock the insert into row of the tableTable...;

UpdateTable...;

Delete fromTable...;

Lock tableTableIn row exclusive mode;

Once the row in the table is locked through RX, the following operations cannot be performed on the table:

Lock tableTableIn share mode;

Lock tableTableIn share exclusive mode;

Lock tableTableIn exclusive mode;

(3) S (share table lock) can lock the table in the following ways:

Lock tableTableIn share mode (it can be said that multiple s lock a table );

Permitted operations, all queries, and select... for update, and lock table... in share mode, which only allows updating data in the transaction (update, which only allows one s lock on the current table)

Operation not allowed

Lock tableTableIn share row exclusive mode;

Lock tableTableIn exclusive mode;

Lock tableTableIn row exclusive mode;

(4) SRX locks the table in the following ways:

Lock tableTableIn share row exclusive mode;

A little less than S. Here, only one SRX is allowed to lock a table.

Operation not allowed:

Lock tableTableIn share mode;

Lock tableTableIn share row exclusive mode;

Lock tableTableIn row exclusive mode;

Lock tableTableIn exclusive mode;

(5) Lock table with the highest level XTableIn exclusive mode;

Only other transaction queries are allowed.

Table 13-4 locks obtained by DML statements

DML statement	Row locks?	Mode of table lock
Select... fromTable
InsertTable...	X	RX
UpdateTable...	X	RX
Delete fromTable...	X	RX
Select... fromTable... For update...	X	RS-
Lock tableTableIn...
Row share mode		RS
Row exclusive mode		RX
Share Mode		S
Share exclusive mode		SRX
Exclusive Mode		X

This table is clear.