Personal insights on Oracle database transaction writing

In the Oracle database, a control mechanism called "transaction" is provided. Through the thing, can complete the data effective security modification operation, causes the data in the database to achieve the data consistent state. A transaction can treat several operational steps of a database as a whole, and when one of the operations is unsuccessful, all operations will be rolled back. The Oracle database is the mechanism through which data consistency is ensured.

650) this.width=650; "src=" Http://183.61.143.148/group1/M00/02/48/tz2PlFQ42JqwqzA9AACJ3hUM028819.png "/>

However, if the transaction is poorly written, it will not only play a role, but also greatly reduce the performance of the database. If the database transaction execution time is relatively long, it is very likely to lead to lock conflict, thereby reducing the concurrency of database access performance. Therefore, there are several guidelines that the database administrator needs to follow when writing transactions.

Guideline one: Minimize the record of access in a transaction

In a transaction, if a record action statement such as update is executed, the database locks the records it accesses in order to ensure data consistency, preventing other users from modifying them at the same time. At this point, if other users need access to the locked record, they must wait. There will be a lock conflict at this point.

Therefore, try to minimize the amount of records that are accessed in the transaction. This reduces the number of locked rows, thus reducing the conflict between transactions. This requires the database administrator to be as precise as possible in the transaction. If you are reading a record in a database, it is best to use the WHERE statement to locate it. And when writing the where statement, it is best to try to be as detailed as possible, preferably one-on.

Guideline two: Keep your business as concise as possible

Minimizing the number of access records in a transaction is a matter of considering lock collisions from a quantitative perspective. Keeping transactions as concise as possible is a matter of thinking about avoiding lock collisions from a time perspective. Keeping transactions as concise as possible is primarily a requirement for database administrators not to write transactions too large and complex when writing transactions. Otherwise, the transaction takes more time to execute. The immediate consequence of this is that the database locks up some records, even a single data sheet, for a long time. This can worsen the negative impact of locking on the database.

Therefore, when the user is aware of the need to make changes to the record, it is necessary to start the transaction immediately, and in the shortest possible time to execute the relevant modification statements, and then immediately submit or rollback. Also, the transaction statement is opened only if it is really necessary. Specifically, the database administrator can try the following methods in terms of the simplicity of the transaction.

1. Do not add too many changes or DELETE statements in the same transaction. such as when the user needs to update the relevant data in the User Information table. For example, the YG prefix is added before the employee's number and the employee's seniority is calculated based on the employee's entry year. These two UPDATE statements are technically not a problem when placed in the same transaction. However, when the number of employees is relatively long, if they are merged together, the transaction will take longer to execute. To do this, it is best to update the database table, if the expected execution time is longer, it is better to be able to split the update statement, such as column updates and so on.

2, in the update, if a one-time update of the statement more, it is best to choose the appropriate times to update. Updating a record in a database is often more time-proportional to the database's record. The more records it has, the more time it takes to update. To this end, I suggest that when the need to update more records, it is best to choose a reasonable time. If some applications are designed, you can put this update in the background processing. In this case, the application can choose to update the records in the table when the database is relatively empty. This can undoubtedly reduce the negative impact of transactions on the database to a large extent.

Guideline three: Do not require user input during transaction processing

When writing a transaction, the database administrator ensures that the database system obtains everything that is required for the transaction to execute before the transaction starts. such as the query criteria for the record, what needs to be updated, and so on. If the user's input is also required during the execution of the transaction, the current transaction is rolled back. After the user has provided the necessary parameters, restart the transaction. Because the user responds immediately even during the execution of the transaction. However, the user's response speed is much slower than the computer's response. Therefore, when the user needs to input parameters during the execution of the transaction, the database resources occupied by this firm will be kept for a long time. This has the potential to increase the risk of blocking. Because when the user does not enter the required parameters in time, the transaction remains active and locks the associated resources until they respond. If the user needs to input more than a few parameters, the user may not be entered for a few minutes or even one hours.

Guideline IV: Try not to open transactions while browsing data

According to the author's experience, the time required for users to change the data is actually very small. Most of the time, the data is analyzed before the data is changed. such as when locating what data needs to be changed, such as in changing transactions to submit a good audit, as in considering how to make the change. This analysis is often dominated by a large head.

Therefore, the author reminds the database administrator, before all the prepared data analysis is completed, in the user data browsing, do not start the transaction. That is, when the user changes the data, it is still not the best time to trigger the update transaction. This transaction can only be triggered by selecting the "Update" button when the user has confirmed the error. Also, submit or roll back the transaction in a timely manner. In this way, the transaction does not continue to occupy resources during the post-audit process.

In addition to these guidelines, there are other minor details to note. The database administrator should choose a reasonable time when designing the transaction related to the update. Let the transaction to protect the data security, while minimizing its negative effects.

If you want to learn more Database Tutorials knowledge can be landed on E mentor Network.

This article is from the "Add Language" blog, please make sure to keep this source http://yuguotianqing.blog.51cto.com/9292883/1563124

Personal insights on Oracle database transaction writing