Summary of Oracle transaction-related management issues

The following article mainly introduces the summary of the issues related to Oracle transaction management. I saw the relevant information about Oracle transaction management on the relevant website two days ago and thought it was quite good. I will share it with you. Issues related to Oracle transaction management.

The data status remains unchanged during the SQL statement operation. Statement-level data status is affected only when this operation is completed. That is to say, the statement may have updated two rows at the same time, but the same primary key is used, this will cause a violation of the uniqueness Convention and eliminate the impact of the entire statement. If the statement is successfully executed, however, the data status may not be affected at the Oracle transaction level. That is to say, if the Oracle transaction is rolled back, the impact will still be eliminated. However, as mentioned above, after a statement is successfully executed, it may affect the data status of other statements. BKJIA database channel recommends the Oracle database debugging and performance optimization topic to you.

How to manage concurrency and recovery Control for PL/SQL Execution?

PL/SQL is executed in a PL/SQL engine. This engine can be considered as an external unit of Oracle. The engine parses PL/SQL statements and continuously sends SQL statements to Oracle. Therefore, there is no essential difference between sending multiple SQL statements in a session connection through JDBC using a Java program. Therefore, concurrency and recovery management are no different.

How does an Oracle deadlock occur?

Since Oracle controls concurrency by using the lock mechanism, a deadlock problem occurs. When Oracle executes a statement, it resolves the lock to be applied and the release time based on the meaning of the statement and the Oracle transaction isolation level. The higher the isolation level, the larger the lock resource occupation. Now, in this case, Oracle processes two transactions A and B at the same time.

A sent several statements, causing Oracle to add several locks not to be released, and B sent several statements, causing Oracle to add several other locks not to be released. Now, A sends another statement, which requires that several locks added by Oracle have been occupied by transaction B, so A waits, and B sends another statement, this statement requires the Oracle lock in the hands of.

The deadlock occurs. The higher the isolation level, the more likely the deadlock is. It can be analyzed that the root cause of the deadlock is that the statements contained in the Oracle transaction are sent to Oracle in multiple entries, and Oracle cannot parse the locks required for all execution at the beginning of the transaction, when is it released.

Who is the blame for the deadlock problem? I understand this: if there is no transaction concept, Oracle will control concurrency at the statement level and there will be no deadlock issues at all. Because Oracle already knows how many locks to add when parsing statements, it will see that if all these locks can be obtained, it will execute, otherwise it will wait. But how can I avoid Oracle transactions in practical applications?

I agree that a new SQL syntax can be created, and the meaning of multiple original SQL statements can be defined in one statement. The length is not a problem, it is not a problem to sacrifice a certain degree of syntactic conciseness. However, the most important thing is that, in a business processing logic, multiple database operations are mixed with other non-database operations, and want to obtain these database operations as a whole ACID.

Therefore, the transaction concept must exist. In this case, we may be able to hand over the statements that may be contained in an Oracle transaction to Oracle at the beginning of the transaction, it may include statements that are not actually executed through business logic judgment, resulting in Oracle wasting locks and reducing concurrent processing capabilities.

My previous articles have introduced how to implement synchronization control using Java to reduce the Oracle isolation level and only use Oracle's atomic support. The reason for doing so is basically mentioned above. When writing Java business logic, we know how many database operations we need in a string of business logic. Therefore, all the locks can be controlled at the beginning of the business logic before execution.

This can indeed reduce the pressure on Oracle and eliminate deadlocks. However, this will cause the synchronization pressure to be concentrated on the Java application end, and the requirements for developers will also increase. Even so, it is much more flexible to use synchronization on the Java application end without having to limit the table lock row. You can even create a forest structure of semaphore data to control synchronization.

In turn, you can understand the isolation level. Why is there an isolation level that allows phantom reading? The existence of the isolation level is a trade-off. If the application does not want to control the concurrency on its own, but also wants to improve the concurrency capability, You need to weigh it out!