Summary of Oracle transaction management related issues

1.oracle How does the execution of an SQL statement manage concurrency and recovery control?

A syntax-compliant SQL statement that defines the operation of the database. At the moment this operation is executed, a data state for the database is mapped. This state can be understood in such a way that no database statement-level operation is being executed concurrently until this execution time, that is, multiple statement-level operations that are actually executing concurrently can be assumed to execute after this statement operation. This emphasizes statement-level operation, which means that if a transaction contains more than one action statement, at this point in time, several of them are actually executed, and a statement is being executed at this time, so it is not easy to assume that the preceding few action statements have not yet occurred, which is to see the isolation level of the transaction, but regardless of the level of transaction isolation levels, The statement level can be considered a sequence execution.

This data state is considered to remain unchanged during the operation of the SQL statement. The statement-level data state effect is generated when the operation completes at the end of the execution time. That is, maybe the statement updates two rows at the same time. But with the same primary key, this can result in a violation of the uniqueness Convention and eliminate the effect of the entire statement, which, if executed successfully, does not necessarily affect the state of the data at the transaction level, which means that if the transaction is rolled back, the effect is still eliminated, but as noted above, Once a statement is executed successfully, it can affect the data state that other statements face.

How does the execution of 2.pl/sql manage concurrency and recovery controls?

The pl/sql is executed in a pl/sql engine. The engine can be considered to be a unit other than Oracle. The engine parses the Pl/sql and sends the SQL statements to Oracle continuously. So there is no essential difference between sending multiple SQL statements in a session connection with a Java program through JDBC. So concurrency and recovery management are no different.

How did the 3.oracle deadlock occur?

Because Oracle controls concurrency is the lock mechanism used, it can also result in deadlock problems. When Oracle executes a statement, it resolves what locks need to be added, and when to release them, based on the meaning of the statement and the level of transaction isolation at which it is based. The higher the isolation level, the greater the lock resource occupancy. Now consider this scenario where Oracle handles two transaction a,b at the same time. A sent over a few statements, causes Oracle to add a few locks did not release, B sent several statements, causing Oracle to add a few other locks did not release, now, a again sent a statement, this statement requires Oracle plus some locks, a few locks have been occupied by B transactions, then a wait, and b sends over a statement that requires Oracle to add a lock to the hand. So the deadlock appears. The higher the isolation level, the greater the likelihood of deadlocks. Can be analyzed, the root cause of deadlock is that the transaction contains statements are sent to the Oracle, Oracle can not at the beginning of the transaction to resolve the full implementation of the process need what locks, when released, can not be unified arrangements.

Who is responsible for the deadlock? I understand this: if there is no transactional concept, Oracle controls concurrency at the statement level, and there is no deadlock problem at all. Because Oracle already knows how many locks to add when parsing a statement, it will look at the current locks if they are all available, or wait. But how can practical applications have no concept of affairs? I agree that there can be a new SQL syntax, you can put the meaning of the original multiple SQL statements defined in a statement, the length is not a problem, the sacrifice of a certain degree of grammatical simplicity is not a problem; But the key is that often we are in a business process logic, Multiple database operations are doped with other non-database operations and want to get these database operations as a whole acid. Therefore the transaction concept must exist. That being the case, perhaps we can really put the statements that a transaction might contain to Oracle at the start of a transaction, although this might include statements that actually do not execute through business logic, causing Oracle to waste locks and reduce concurrent processing power.

My previous article has described the use of Java to implement synchronous control, reduce the level of Oracle isolation, only the use of Oracle's atomic support. The reason for doing so is basically mentioned in the above. When we're writing Java business logic, we know how many databases we need to manipulate in a bunch of business logic, and so we can control all the lock executions at the start of the business logic. Doing so can reduce Oracle pressure and eliminate deadlock problems, but doing so will lead to a convergence of synchronization pressure to the Java application, as well as increased demand for developers. Still, using synchronization on the Java application side is much more flexible than locking the table lock, and you can even build a forest-structured semaphore to control synchronization.

Well, in turn, you can understand the isolation level of the problem, why do you have to allow Phantom read the isolation level? The existence of isolation level is a trade-off, if the application does not want to control concurrency, but also want to improve concurrency capabilities, you need to weigh it!