JDBC transaction operations on the database

JDBC transaction operations on the database itpride (original)

Keyword JDBC, transaction

JDBC transaction operations on the database

Ginkou. Fly 2002-9-4

1. Overview:

In JDBC database operations, a transaction is an inseparable unit of work composed of one or more expressions. We end the transaction operation by committing commit () or rolling back rollback. The transaction operation methods are located in the Java. SQL. connection interface.

2. features:
★In JDBC, transaction operations are automatically committed by default. That is to say, an update expression for the database represents a transaction operation. After the operation is successful, the system will automatically call commit () to submit the operation. Otherwise, the system will call rollback () to roll back and forth.
★In JDBC, you can call setautocommit (false) to disable automatic submission. Then, the expressions of multiple database operations can be used as a transaction. After the operation is complete, commit () can be called to submit the entire transaction. If one of the expressions fails to be operated, will not execute to commit (), and will generate a response exception; in this case, you can call rollback () for rollback when capturing exceptions. In this way, Data Consistency can be maintained after multiple update operations, as shown in the following example:

Try {

Conn =

Drivermanager. getconnection

("Jdbcracle: thin: @ host: 1521: Sid", "username", "userpwd ";

Conn. setautocommit (false); // disable automatic submission and set the rollback point.

Stmt = conn. createstatement ();

Stmt.exe cuteupdate ("alter table ..."); // Database update operation 1

Stmt.exe cuteupdate ("insert into table ..."); // Database update operation 2

Conn. Commit (); // transaction commit

} Catch (exception ex ){

Ex. printstacktrace ();

Try {

Conn. rollback (); // rollback if the operation is unsuccessful

} Catch (exception e ){

E. printstacktrace ();

}

}

 

★The jdbc api supports database locking by transactions and provides 5 types of operation support and 2 types of locking density.

Five types of support:

Static int transaction_none = 0;

→ Prohibit transaction operations and lock.

Static int transaction_read_uncommitted = 1;

→ Dirty data read/write (dirty reads), repeatable reads, and phntom

Reads)

Static int transaction_read_committed = 2;

→ Disable dirty data read/write (dirty reads) and allow repeatable reads and phntom reads)

Static int transaction_repeatable_read = 4;

→ Disable dirty data read/write (dirty reads) and repeatable reads, and allow image read/write (phntom reads)

Static int transaction_serializable = 8;

→ Disable dirty data read/write (dirty reads), repeatable reads, and allow image read/write (phntom reads)

Two density types:

The last entry locks the table, and the remaining 3 ~ Four locks the row.

Dirty data read/write (dirty reads): when a transaction modifies the value of a Data row but is not committed, the other transaction reads the value of this row. If the previous transaction is rolled back, the next transaction will get an invalid value (dirty data ).

Repeatable reads: when a transaction reads a data row, the other transaction simultaneously modifies the data row. The previous transaction reads this row repeatedly and returns an inconsistent value.

Phantomreads: when a transaction queries data in a table, another transaction inserts data rows that meet the query conditions. When the previous transaction repeatedly reads the value that meets the condition, it will get an additional "image" value.

JDBC sets transaction support and Lock Based on the default value provided by the database. Of course, you can also set it manually:

Settransactionisolation (transaction_read_uncommitted );

You can view the current settings of the database:

Gettransactionisolation ()

Note that the database and its driver are used for dynamic settings.ProgramThe corresponding transaction operations must be supported.

With the increase of the value, the independence of the transaction increases, which can effectively prevent conflicts between transaction operations. It also increases the lock overhead and reduces the concurrency of database access between users, the program running efficiency is also reduced. Therefore, you must balance the conflicts between program running efficiency and data consistency. Generally, you can use the transaction_read_uncommitted method to query databases only. For operations with far more data queries than updates, you can use the transaction_read_committed method. For more update operations, transaction_repeatable_read can be used. When data consistency requirements are higher, consider the last item. Because table locking is involved, the program running efficiency will be greatly affected.

In addition, in Oracle, the default value of the database driver for transaction processing is transaction_none, that is, transaction operations are not supported. Therefore, you need to manually set it in the program.

3. Summary

JDBC provides complete support for database transaction operations. Transaction operations can improve program running efficiency and maintain data consistency.