Oracle transaction row-Level Lock storage point rollback commit

Oracle transaction row-Level Lock storage point rollback commit. Note that A and B are not two ORACLE accounts, but two connection sessions. The above is just

Oracle transaction row-Level Lock storage point rollback commit. Note that A and B are not two ORACLE accounts, but two connection sessions. The above is just

Oracle transaction

A general transaction (DML) is a transaction for data modification (addition, deletion, and modification ).
The transaction adds a lock (Row-Level Lock) to all the data rows modified in the transaction to prevent others (sessions) from simultaneously modifying the data.
After a transaction is committed or rolled back, the data will be released.

For example:
After A operates on A data N1, no transaction is committed. Then B operates on the same data N1 again. The situation is as follows:
1. All persons except A cannot see the data modified by.
2. B is waiting until A submits or rolls back the modification to the data (this is the concept of Row-Level Lock)
3. After A commits A transaction, everyone can see the data modified by A and the data modified by B. However, B can see the modified data (the same as A, because B has not committed the transaction ).
4. After B commits a transaction, all users can see the modified data of B.

From the above we can see that the transaction processing for the same piece of data must be performed in sequence.
That is, after A commits A transaction, B can commit the transaction. Otherwise, B cannot modify the data (B is in the waiting state when operating the data, until A commits or rolls back the transaction and releases the lock for this row of data ).

Note that A and B are not two ORACLE accounts, but two connection sessions. The above is just for convenience.
That is to say, even if the same ORACLE account is used on the same computer, two connection sessions are opened at the same time (for example, two PL/SQL statements are opened at the same time and connected to the same database with the same account ), it is regarded as two people A and B.

In PS/SQL, if the user does not submit a transaction but closes or disconnects PS/SQL after the data is modified, ORACLE will submit the transaction for this session immediately.

We can use the save point in the transaction to roll back to the specified time node, but if the transaction is committed (commit), all the Save points will be deleted.

For example:

Savepoint a1; -- set a storage Point a1;

Update tmp set username = 'zhang san' where userid = '000000' -- modify a data record

Savepoint a2; -- set a storage Point a1;

Update tmp set username = 'lily' where userid = '000000' -- modify a data record.

Rollback to a2; -- roll back to the a2 save point. All data modifications after the a2 save point are considered invalid.

Rollback to a1; -- here, You can refresh from the a2 save point to the a1 save point. In this case, all data modifications after the a1 save point are considered invalid.

Rollback; -- roll back all. That is, all data modifications after the transaction is committed are revoked.

Commit; -- commit the transaction to commit the rolled back transaction and delete all save points.

Note: We can roll back from a2 to a1, but not from a1 to a2. That is to say, you can only roll back from the previous point!

Read-only transactions are transactions that are only used for select queries.

Read-only transactions are mainly used for data query statistics at a certain time point. For example, you can create a read-only transaction at and then start to count the number of records in a table. At this time, the data added to the table after will not enter the statistics. That is, the session connection person who creates a read-only transaction will not be able to see any other real modification to the data after the read-only transaction is created.

The method is:

Set transaction read only; -- after this, the user who owns this session cannot see any data changes made by others. You can use select to query statistics at this time point.

Set transaction read write; -- cancels a read-only transaction

Read-only transactions do not have the rollback and commit functions and do not record rollback logs.

The biggest use of read-only transactions is to ensure the consistency of query results at a certain point in time. In other words, if you want to count a lot of information, when there are multiple select queries, but after you finish the first query (such as user information statistics ), someone else may have modified some of the data you are querying. Then you can perform the second query (such as user information statistics or other queries on user information ). At this time, the data query results are inconsistent. The results of the second query are different from those of the first query because the data is modified. Read-only transactions are the solutions to these problems.

In fact, ORACLE will also start read-only transactions for a single query to optimize the query (that is, there will be a read-only transaction point when you start to execute the query, until the query ends, during this period, data modifications are ignored. You do not need to manually create read-only transactions. However, when there are multiple queries, We must manually create read-only transactions.