JDBC transaction optimization
Author: Jack Shirazi
Develop applications through ACID testing
The business makes the developer's work simpler. When you use transaction functions in JDBC APIs and relational databases such as ORACLE9I, you can minimize the possibility of data damaged by multiple user applications. However, transaction needs to handle overhead, compared to free transaction applications (more easily destroyed), which reduces the performance of the system. So, when using a transaction, what is the best way to maintain performance?
The best performance tuning proposal is to avoid doing things that are unnecessary. Transaction processing is a large number of works in the database, and the database defaults to maintain a variety of resources to ensure that the transaction has ACID (atomic, consistency, isolation, and continuous) attributes (see the "Acid Transaction Properties" toolbar for details). These database resources manage multiple data concurrent operations and submission and rollback operations to ensure the ACID transaction property. If you reduce this type of database, you will increase the performance of the application. Let us see some methods to avoid handling overhead and improve transaction performance.
Automatic submission mode
The first method for minimizing business overhead is to merge transactions by moving multiple operations to a single transaction. By default, JDBC connection works in automatic submission mode, which means that each operation sent to the database is automatically executed as a stand-alone transaction. In this case, each statement.execute () method call is as follows by a begin transaction command, and ends by a commit command.
Turn off automatic submission mode and explicitly define transactions need to make a lot of extra work, because you must manually add a transaction division statement (Commit and Rollback). However, merging transactions can reduce performance overhead, especially when you telescopic for your system. (The "Batch Update" section will involve the technical details of the merger update transaction.) In the heavy load system, the transaction overhead significance is significant. The lower the overhead, the better the system's scalability.
Simply use the Connection.SetAutoCommit (False) command to turn off the auto submission mode.
The JDBC API also provides a connection.getautocommit () method to return the current auto submission mode. When you turn off automatic submission mode, you will need to use two transaction division methods: connection.commit () and connection.rollback ().
When manual control of affairs, you need to follow the following principles: Make the transaction as much as possible, do not contain many operations in a transaction to make them very lengthy. (Keep the transaction and keep the line unlocking state, affect other transactions and reduce scalability.) However, if several operations can be executed one item, then merge them into a transaction.
Merging operations may require additional conditional logic in your SQL statement and may require a temporary table. Regardless of this overhead, the merging transactions will be more effective because the database can get all the required locks in one step and release them in one step.
When the auto submission mode is not closed, more transactions that it cause will generate more communication overhead, more lock and release time, and more likely to conflict with other sessions.
Bulk update
Batch updates simply say that multiple DML statements (such as insert, updates, and delete) are sent to the database in a transaction and a database call. JDBC supports this feature via statement.addbatch () and statement.executebatch () methods. Batch updates are quite simple, and will be explained below. Remember to close automatic submission mode (make sure a batch is executed as a transaction), and after all of which you have completed it, clearly submit a batch of affairs. The example in Listing 1 uses a normal JDBC Statement object. In addition, the JDBC API provides a PREPAREDSTATEMENT class, which can also represent the SQL statement with parameters.
Also, when you use the PreparedStateMent object instead of the Statement object, Oracle's JDBC batch implementation can be optimized. In Oracle JDBC, the Statement object does not transmit all batch SQL statements in a network transfer. Due to this limitation, the preparedStatement object can be used when batch transmission statements, because preparedStatement transmits all statements in a batch.
Listing 2 gives the same batch skills using the parameter statement and the PreparedStatement object.
With the same query plan with all batch statements, use the parameter statement in the PreparedStatement object to further optimize batch processing. If there is no parameter setting, the statement will vary, so the database cannot reuse the query plan.
Although this method often improves performance, it should be aware of the following points: The following points: The joints of the processing overhead and the creation of the query plan will result in the first execution SQL statement than using the ordinary statement object, and then ready Executing statements will be much faster. (Developers often move the first PREPAREDSTATEMENT batch from the application to the application of the time required.) Use the PreparedStateMent object to be more efficient than using the Statement object, especially when using a large amount of manner to more than 50 statements. The above examples use the standard batch mode defined by the JDBC specification. Oracle's JDBC implementation provides an alternative batch mode that uses a new method called OraclePrepareDStatement.seTexecutebatch (int). In this mode, the preset statement is automatically saved to the client, until the number of statements is equal to the "bulk" defined by the parameters in the setExecutebatch (int). In this way, the accumulated statement is sent to the database in one transfer. This model recommended by Oracle is faster than the standard batch mode in some cases. When using it, adjust the bulk value to optimize the performance of your transaction in your application. Oracle mode The only thing to note is: it is not a standard - it uses the extension function that is not supported by the official JDBC specification.
Transaction isolation level
The transaction is defined as all or all operations. The ACID attribute of a transaction ensures that everything happens in a transaction, as in the transaction, does not have other actions in the database. It can be seen that the database is ensured that the ACID attribute has a lot of work.
The JDBC Connection interface defines five transaction isolation levels (below below). Not all databases support all levels. For example, Oracle9i only supports transaction_read_committed and transaction_ serializable these two levels.
Many databases such as ORACLE9I provide other transaction level support. These levels do not provide "real" transactions because they are not fully conforming to the ACID attribute. However, they provide better performance through acceptable transaction functions, so they are very useful for many operation types.
The level defined in JDBC includes: Transaction_none. It is officially speaking that Transaction_None is not an effective transaction level. According to the Java.sql Connection API file, this level indicates that the transaction is not supported, so you can't use Transaction_None as an argument to the Connection.SetTransactioniSolation () method. In fact, although some databases have implemented this transaction level, Oracle9i is not implemented.
Transaction_read_uncommitted. This is the fastest fully and effective level of transaction. It allows you to read other modifications made that it has not been submitted to the database. This API file indicates that dirty reads, non-refeatable reads and error reads (see "Some Non-Agent Problems" section). This level is intended to support the ACID "Atomic" section. In this level, if your modification is submitted, it will be considered simultaneously; if it is revoked, it is not happened. Oracle9i does not support this level.
Transaction_read_uncommitted. This is the fastest fully and effective level of transaction. It allows you to read other modifications made that it has not been submitted to the database. This API file indicates that dirty reads, non-refeatable reads and error reads (see "Some Non-Agent Problems" section). This level is intended to support the ACID "Atomic" section. In this level, if your modification is submitted, it will be considered simultaneously; if it is revoked, it is not happened. Oracle9i does not support this level.
Transaction_read_committed. This is the fastest and valid level after transaction_read_uncommitted. In this level, you can read the modifications made by other concurrent services that have been submitted to the database. The API file indicates that the dirty reading is prohibited in this level, but it can happen without repeated reading and error reading. This level is the default level of Oracle9i. Transaction_repeatable_read. This level is fast than Transaction_Serializable, but is slower than other transactions. The read operation can be repeated, which means that the same domain should always be obtained twice, unless the transaction itself changes this value. The API file indicates that dirty reading and non-repeatable read are disabled in this transaction, but error read can occur. Technically, the database implements this level by locking on rows being read or written, and keeps the lock state until the transaction ends. This prevents these rows from being modified or deleted, but it is not possible to prevent additional rows from being added - therefore, it is possible to generate an error read. Oracle9i does not support this level.
Transaction_serializable. This is the slowest level of transaction, but it is fully compatible with ACID. "SERIALIZABLE" refers to ACID compatibility, where your transaction is considered to be overall, just like all other submitted transactions, before or after this transaction. In other words, the transaction is executed serially. ACID transaction attribute
Atomic, consistency, isolation, and persistence of ACID refers to ACID. All Oracle transactions are all in line with these properties, although you can make a manual setting level in the database system (see "Transaction Isolation Levels" section). Atomicity is that the entire activity sequence in the transaction must be completed or all given up. The transaction cannot be completed in part. After combining the isolation (see Table 1), atomicity is that any one of the activities will be taken from any other or atomic forms of affairs. Consistency means that transactions can create a new, effective data state (all modifications can be made in this state), which can return all data to transactions in the event of operation failure. status. Isolation refers to all activities that occur in a transaction to be invisible to other affairs until the transaction is submitted. Continuity refers to all changes that the transaction succeed and all changes are constant, and the system failure must be overcome. For example, if a fault or system restart occurs, the data is available in the event of final submission of the transaction.
Dirty reading, non-readable and error read in the Transaction_Serializable level is all disabled. Technically, the database implements this level by locking the table used in the transaction. Oracle9i supports this level (just as each of which is like the ACID database).
Develop applications through ACID testing
Select the correct level
You can set a connected transaction level by using the Connection.SetTransactioniSolation () method. Similarly, you can get the current transaction level of the connection by using the Connection.getTransactioniSolation () method. You can determine the transaction level supported by your database driver by using the DatabaseMetadata.supportstransaction IsolationLevel () method, as shown in Listing 3.
The higher the level of the transaction, the more the number, the limitable lock will be used to database records or tables. At the same time, more locks are used to database and their coverage, the greater the possibility of any two transaction conflicts.
If there is a conflict (such as two transactions trying to get the same lock), the first transaction will be successful, but the second transaction will be blocked until the first transaction releases the lock (or trying to get the lock. Timeout leads to the failure of the operation).
When more conflicts occur, the execution speed of the transaction will slow down because they will spend more time for resolving conflicts (waiting to be released).
Maximize the scalability of the application needs to balance the transaction execution method. On the one hand, you can optimize the application by minimizing the amount of operations performed in the transaction, thereby reducing the time spent on a single business. But this increases the total number of transactions, which may increase the risk of conflicts. With bulk operations, you can minimize the number of transactions executed.
However, this increases the length of a single transaction, or it may increase the risk of conflicts. In any case, when you reducing the transaction isolation level, the less the lock is used, so the less performance will drop. The risk of doing this is because it is not used in full compliance with ACID, thereby losing functionality.
If you need to minimize transaction execution time, you don't seem to be ideal in your entire application. Find a read query in the application. For each query, consider whether any of the issues listed below "Some Non-Affairs Transaction Questions" sections will have a negative impact on the query or data update mode of the given data. Read the static table or only read the tables updated by their same transactions, which can safely use the lowest transaction level. In transactions that are impossible to make concurrently updated, you can safe and efficiently use the level such as Transaction_ Read_committed. Some non-ACID issues
There is a lot of problems when a connection uses the Transin_Serializable transaction level that is incompletely conforming to the ACID. The following example uses a table named table_sizes, which has two fields, TableName and Tablesize. This example also uses two transactions, T1 and T2, where T1 uses Transaction_Serializable levels.
Dirty reading. When a transaction can find a change in a line, a dirty reading occurs. If another transaction changes a value, your transaction can read the changed value, but other transactions will roll back their transactions, making this value invalid or become dirty. For example, the case where T2 occurs when T2 uses transaction-level transaction_ read_uncommitted, which is recorded as Tablename = Users, TABLESIZE = 11.
Table 1: The possibility of each transaction problem occurs in each allowed transaction isolation level.
Isolation Level Dirty Read Innotable Read Error Insert Read Unloaded Data Yes Yes Yes Yes Submiting Dix No Yes No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No
1. T1 and T2 launch their transactions. 2. T1 will update the record to TABLENAME = Users, TABLESIZE = 12.3. T2 reads T1 uncommitted modification, read record tablename = user, tablesize = 12, because T2's transaction level means that the uncommitted modification is sometimes Read. 4. T1 rollback transaction, so TableName = 10.5. TABLESIZE = 11.5. T2 still has an invalid record value: record Tablename = Uses, TABLESIZE = 12. However, it can work through dirty table size and can be successfully submitted on the basis of the dirty value.
Do not read it repeatedly. When a record in the transaction is not read in the transaction, it is not readable, and different results can be seen from the two readings. If your transaction reads a value, and another transaction submits a modification of this value (or deletes this record), then your transaction can read this modified value (or discover this record lost Although your business has not been submitted or rolled back. For example, the case where T2 occurs when T2 uses transaction level transaction_read_committed, which is recorded as Tablename = Users, TABLESIZE = 11.
1. T1 and T2 launch their transactions. 2. T2 Read Record Tablename = Users, TableSize = 11.3. T1 will update the record to Tablename = Users, TABLESIZE = 12 and submit a modification. 4. T2 Re-reading Record Tablename = Uses, and now see tablesize = 12, because T2's transaction level means that modifications submitted by other transactions can be seen, although T2 has not been submitted or rolled.
Error reading. An error read occurs when a transaction reads by another unrolled transaction insertion. If another transaction is inserted into a table, you can read that new record when your transaction queries that table, even if other transactions roll back. For example, a case that occurs when T2 occurs when T2 uses transaction level transaction_repeatable_read, which is recorded as Tablename = Users, TABLESIZE = 11.1. T1 and T2 Start their transactions. 2. T2 Execute Select * from table_sizes where tablesize> 10 and read a line, TableSize = 11 rows TableName = User. 3. T1 is inserted into the record of Tablename = Groups, TableSize = 28. 4. T2 performs select * from table_sizes where tablesize> 10 and reads two records: TableName = Users, TableSize = 11 and TableName = Groups, tablesize = 28.5. T1 rollback transaction, therefore logging Tablename = goups, TableSize = 28 No longer existed in the table_sizes table.
An additional error record in the data set read according to T2 is successfully submitted.
In Table 1, you will find a list of possible issues in each of the allowed event isolation levels.
User-controlled transaction
In many applications, users must perform a clear action (such as clicking "OK" or "Cancel"). These situations will result in many problems. For example, if the user has forgotten termination or not complete, the resource remains open in the application and database, which may have conflicts between concurrent activities and locked resources, reducing the system. performance. Only single user applications or applications that users do not share resources are not affected by this issue.
The main solution to allow users under a JDBC transaction control is to use optimized transactions. These transactions are external JDBC transaction update collection information, and then use a mechanism to check that the update does not have conflicts with any of the other may have been processed between the two.
Checking the mechanism for optimizing conflicts comprises using a time tag or change counter, from the desired state check. For example, when the application collects data used to update from the user, the data can be sent to the SQL statement containing the time tagged security mechanism to ensure that the original data in the database is originally used for the client application. The data is the same. A successful transaction update record, including the time tag, which shows the most recently modified data. If the update from another user makes the first user's modification, the time tag will change, and the current transaction will need to be rolled out without being submitted. For many applications, the medium-level conflict matters is very small, so transactions often completed successfully.
When a transaction fails, the application submits the input data to the user, allowing users to make the necessary modifications and re-submit them based on changes that lead to conflicts.
Other considerations
In the future, developers who use JDBC3.0 features will optimize the transaction in more ways. For example, Oracle9i 2nd Edition implements several JDBC3.0 features, including transactional storage points (SavePoint). The storage point allows you to mark a point in a transaction and roll back it, not the entire transaction.
Although this sounds a bit incredible, the storage point is indeed greatly reduced performance overhead. The actual impact of performance will prove that JDBC3.0 will be more widely supported, but do not use storage points or avoid placing them together in any key performance code section. If you do use them, you must ensure that their resources are released as much as possible using the Connection.Release SavePoint (SavePoint) method. Currently, JDBC2.0 supports distributed transactions across multiple connections, and Oracle provides a Java Transaction API (JTA) module designed for distributed transaction. The decision to implement an external transaction manager for distributed transactions is not a small thing. However, distributed transactions are obviously slower than ordinary transactions because it requires additional communication work to coordinate connections between multiple database resources. From a pure performance point of view, it is best to consider a variety of optional design prior to transferring to a distributed transaction architecture.
Jack Shirazi (Jack@javaperformancetuning.com) is the supervisor of javaperformancetuning.com (a first resource site for all aspects of the Java application performance tuning), is also "java performiate tuning" (published) Author of the book.
