Oracle transaction processing and instance demonstration jdbc operation batch Delete, oraclejdbc

Oracle transaction processing and instance demonstration jdbc operation batch Delete, oraclejdbc

Transactions

As the basic unit of logical processing, database operations are composed of one or more SQL statements. Of course, there are also non-database operations, such as the Restore Point Set in the computer is a good application.

The basic nature of transactions is described in another article: SQL transactions and instance demonstrations.

 

Transaction differences between oracle and SQL server

 

Transactions in SQL server are generally classified into implicit transactions, explicit transactions, and automatically committed transactions.

Automatic transaction: for SQL server, when the client submits an SQL statement, SQL server automatically starts a transaction. For such a transaction, it is automatically submitted after the SQL statement is executed.

Display transaction: this is also a common transaction. In fact, in the automatic transaction, add a Begintran, conn. commit, end tran.

Implicit transactions: Compared to transactions that require enabling connection, implicit transactions enable the Begin of transactions and database connections by default. Of course, you still need to perform the commit or rollback operation later.

Oracle transactions are not so rich, similar to SQL server's implicit transactions. You do not need to enable conn and Begin, as long as you perform commit or rollback operations in subsequent operations.

 

Transaction commit Mechanism

 

First, I want to understand some basic concepts: data buffer cache: A High-Speed Read-Only cache that connects a hard disk file to oracle data operations.

SGA: A memory space opened in the memory after the oracle instance is started, used to store server control information and data.

Data Block: The basic unit of data storage.

After connecting to the database, oracle creates an independent process-shadow process for the connected user, which is accompanied by the user's entire operation;

1. Check data blocks

2. Construct an undo data block

To roll back and forth data

3. Generate redo logs

Logs used for Reoperation are stored in the log buffer cache.

4. The lgwr process starts, commits transactions, and writes all log files.

 

Java Transaction Processing

 

TestDemo: combined with the PreparedStatement interface under java. SQL and oracle transactions, batch deletion is achieved.

Common Methods:

Int [] executeBatch ():

Submit a batch of commands to the database for execution. If all commands are successfully executed, an array consisting of update counts is returned.

VoidsetString (int parameterIndex,
String x ):

Set the specified parameter to a given Java String value. When you send this value to a database, the driver converts it to an SQL VARCHAR or LONGVARCHAR value.

 

Demo

 

/** Delete user-August 11, 2014 18:19:04 * @ userId user ID array */publicBoolean DeleteUser (String [] userId) {// a thread-safe variable String StringBuffersb = new StringBuffer (); sb. append ("deletefrom t_user where user_id =? "); Connectionconn = null; PreparedStatementpsmt = null; Booleanflag = false; conn = DButil. getConnection (); try {// close automatic commit transaction conn. setAutoCommit (false); // create a PreparedStatement object to send parameterized SQL statements to the database. Psmt = conn. prepareStatement (sb. toString (); // Add a set of parameters to the batch processing command of this PreparedStatement object. For (inti = 0; I <userId. length; I ++) {psmt. setString (1, userId [I]. trim (); psmt. addBatch () ;}// execute batch update psmt.exe cuteBatch (); // after the statement is executed, submit the conn transaction. commit (); flag = true;} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace (); try {conn. rollback ();} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace () ;}} returnflag ;}

Summary

In general, I still have a limited understanding of many internal oracle mechanisms. The design model lectures a few days ago also reminded me that some of the things here had this blog. It can only be said that it is very simple to understand that oracle's transaction processing here is essentially consistent with the transaction processing.

Can I access the ORACLE database using JDBC and perform the DELETE operation using JAVA multithreading? How should I configure an ORACLE server?

As mentioned above. Oracle has its own lock mechanism. Even if you open 100 lines, it will be deleted one by one. You cannot delete multiple objects at the same time.
For a large amount of data updates, Oracle provides some optimization measures.
1. Disable auto-commit first. Because each time you delete a piece of data, oracle will automatically execute a commit. Commit requires resources. Therefore, if you manually set 1000 data records to be deleted and execute a commit command, you can save resources.
2. Make full use of batch update. If you do not need batch, each delete command must be uploaded from the network to oracle. 10 thousand delete commands, 10 thousand transfers required. If you bind 100 delete commands to Oracle for execution. You only need to transmit the data for 100 times. Greatly shorten the required time and network resources.
The above suggestions can be found in Oracle reference.

For example, if you want to perform operations on Table A in Oracle, check whether the query needs to be placed in the transaction before updating.

According to your requirements, queries do not need to be placed in transactions. transactions are generally used only when multiple updates or deletions exist, in addition, "multiple updates or deletions" require atomicity, that is, they are either executed or not executed. In this case, you cannot use transactions. You can directly query and update the transactions first. First, transactions are not used for queries, and transactions are not used for multiple consecutive query pages. If the update in step 2 involves only one SQL statement, transactions are not used either. (If Multiple SQL statements are used continuously for updates, transactions are required ).