Common Oracle Transaction Management Problems

The following articles mainly discuss Oracle transaction management related issues, as it includes how Oracle executes related SQL statements and controls the concurrency and recovery of Oracle transaction management? The following is an introduction to the main content of the text. I hope you will gain some benefits.

1. How Does Oracle execute an SQL statement to control the concurrency and recovery of Oracle transaction management?

A syntax-compliant SQL statement defines database operations. The execution time of this operation corresponds to a data status of the database. This state can be understood as follows: no database statement-level operations are being executed concurrently until this execution time; that is to say, multiple statement-level operations that are being executed concurrently can be assumed to be executed after the statement operation.

Statement-level operations are emphasized here. If a transaction contains multiple operation statements, several of them have been actually executed at this time, and a statement is being executed at this time, therefore, we cannot simply think that the previous several executed operation statements have not yet occurred. This depends on the isolation level of the transaction, but no matter what the isolation level of the transaction is, statement level can be considered as sequential execution.

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, it may not affect the data status at the transaction level, that is, if the transaction is rolled back, the impact will still be eliminated, but as mentioned above, after a statement is successfully executed, it may affect the data status of other statements.

2. 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, the concurrency and recovery of Oracle transaction management are no different.

3. How does an Oracle deadlock occur?

Oracle controls concurrency by using a lock mechanism, which can lead to deadlocks. When Oracle executes a statement, it will parse the lock to be added and release at the transaction isolation level based on the meaning of the statement. 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 transaction are sent to Oracle in different parts, 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 we eliminate the concept of 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 a transaction may contain 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 of Oracle transaction management. Why is there an isolation level that allows phantom read? 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!

Article by: http://www.programbbs.com/doc/3298.htm