Oracle Transaction Processing and example demo JDBC Operations Bulk Delete

Transactions

as the basic unit of logical processing, for database operations, one or more SQL statement to form. Of course, there is a very good application for non-database operations, such as a restore point set on a computer.

The basic nature of the transaction is described in one more article: SQL Transactions and example demonstrations

Oracle and SQL Server differ on transactions

SQL Server the transactions in the general are divided into implicit transactions, explicit transactions, and self-committed transactions.

self-motivated business: for SQL Server , when the client submits a SQL statement, this time SQL Server will initiate a transaction on its own initiative, and for such a transaction, after running SQL statements are submitted on their own initiative.

Show transactions: This is also a relatively common use of the transaction, in fact, in essence, on their own active transaction, add a Begintran , Conn.commit,end Tran.

implicit transactions: Need to be opened compared to display transactions Connection , the implicit transaction is the default-enabled transaction Begin and the database connection. Of course, there is still a commit or rollback operation to follow.

and Oracle the business is not so rich, and SQL Server The implicit transaction is similar; no need to open Conn and the Begin , you just have to do it in a maybe Commit or rollback operation.

mechanism for transaction submission

first understand some of the basic concepts, Data Buffer Cache : Equivalent to a piece of attached hard disk files and Oracle Fast read-only caching of data operations.

SGA : On startup Oracle instance, a piece of memory space opened up in memory to hold the server's control information and data.

Data blocks: The basic unit of data storage.

When you connect to the database, Oracle Create a separate process for the connected user - The shadow process, which accompanies the user's entire operation;

1. Check the data block

2. constructing the undo Data Block

To roll the data back and forth

3. generate redo logs

logs for another operation are stored in the Log Buffer Cache the.

4.LGWR The process starts, commits the transaction, and writes all the log files

java transaction processing

Testdemo : Combined in java.sql the following interfaces PreparedStatement and Oracle Transaction Implementation Bulk Delete

Regular usage:

int[] ExecuteBatch ():

A batch of commands is submitted to the database to run, assuming that all commands run successfully, an array of update counts is returned.

void setString (int Parameterindex,
String x):

Sets the specified number of parameters to the given Java String value. When this value is sent to the database, the driver converts it to a 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 mutable string STRINGBUFFERSB =new StringBuffer (); Sb.append ("Deletefrom t_user where user_id =?"); Connectionconn=null; PREPAREDSTATEMENTPSMT = null; Booleanflag=false; Conn=dbutil.getconnection (); try {//Turn off Self commit transaction conn.setautocommit (FALSE);//Create a PreparedStatement object to convert the The SQL statement is sent to the database. psmt= conn.preparestatement (sb.tostring ());//Adds a set of parameters to the batch command for this PreparedStatement object. for (inti =0; i<userid.length;i++) {psmt.setstring (1,userid[i].trim ());p smt.addbatch ();                                }      Run the batch update   psmt.executebatch ();   Statement run complete, submit this transaction   Conn.commit ();     

Summary

Overall, the feeling is still right Oracle A lot of internal mechanisms are limited in understanding. A few days ago the design mode lectures, also let think of here some things have this blog. Just to be able to say is very superficial cognition,Oracle in the transaction here is compared with the processing of the transaction is essentially consistent. data communication across networks or processes should be coarse-grained, not fine-grained. In other words: The client and the database should not be frequently interacting, wasting resources, or the process is the same. The transaction between databases is also a reflection of such a thought.

Oracle Transaction Processing and example demo JDBC Operations Bulk Delete