If only one query is submitted, is it necessary to use a transaction?

Http://www.blogjava.net/terry-zj/archive/2005/12/06/22792.html

 

Http://forum.javaeye.com/viewtopic.php? T = 1603

 

However, there is no clear conclusion. Let's take a look at the transaction definition:

Reference:

Transactions are described in terms of ACID properties, which are as follows:
N atomic: All changes to the database made in a transaction are rolled back if any
Change fails.
N consistent: the effects of a transaction take the database from one consistent
State to another consistent state.
N isolated: the intermediate steps in a transaction are not visible to other users
The database.
N durable: when a transaction is completed (committed or rolled back), its effects
Persist in the database.
 

The definition of acid seems to have nothing to do with read-only queries Except isolated. Do read-only queries require no transactions?

Let's look at Oracle's definition of read-only transactions:

Reference:
Read-only transactions
By default, Oracle guarantees statement-level read consistency. the set of data returned by a single query is consistent with respect to a single point in time. however, in some situations, you might also require transaction-level read consistency. this is the ability to run multiple queries within a single transaction, all of which are read-consistent with respect to the same point in time, so that queries in this transaction do not see the effects of intervening committed transactions.

If you want to run a number of queries against multiple tables and if you are not doing any updating, you prefer a read-only transaction. after indicating that your transaction is read-only, you can run as your queries as you like against any table, knowing that the results of each query are consistent with respect to the same point in time.

By default, Oracle ensures read consistency at the SQL statement level. That is, during the execution of this SQL statement, it only displays the data status before execution, the status of data changed by other SQL statements during execution is not displayed.

The read-only transaction ensures transaction-level read consistency, that is, multiple SQL statements executed within the transaction range can only see the data status at the previous point of execution, without seeing any status changed by other SQL statements during the transaction.

Therefore, we can conclude that:

If you execute a single query statement at a time, you do not need to enable transaction support. By default, the database supports read consistency during SQL Execution;
If you execute multiple query statements at a time, such as statistical queries and report queries, multiple query SQL statements must ensure the overall read consistency. Otherwise, after the previous SQL query, before the next SQL query, if the data is changed by other users, the overall statistical query displays read data inconsistency. At this time, transaction support should be enabled.

Differences between read-only transactions and read/write transactions

For read-only queries, you can specify the transaction type as readonly, that is, read-only transactions. Because read-only transactions do not have data modifications, the database will provide some Optimization Methods for read-only transactions. For example, Oracle does not start rollback segments for read-only transactions and does not record rollback logs.

In JDBC, the method for specifying read-only transactions is:
Connection. setreadonly (true );

In hibernate, the method for specifying read-only transactions is:
Session. setflushmode (flushmode. Never );
At this time, Hibernate also provides some Optimization Methods for read-only transactions in terms of sessions.

In the hibernate encapsulation of spring, the method for specifying read-only transactions is as follows:
Add "readonly" to the prop attribute in the bean configuration file"

 

I tried it at mysql4.1. The process and results are as follows:

Database: mysql4.1
Table type: InnoDB
Spring: 1.1.2
Hibernate: 2.1.7

Use spring declarative Transaction Management

The test process is as follows:

Do not set the transaction type of the query method (that is, no transaction is required): Access the query page, and execute the spring bean method in the background so that hibernate can send the SELECT statement, then manually modify the field value of the record in MySQL, and then visit the query page. The modified field value is not changed, and the log output by Hibernate is displayed, the database still returns the old field value without returning the new field value.

Set the transaction type (read-only transaction) of the query method: access the query page, execute the spring bean method in the background, ask hibernate to send the SELECT statement, and then manually modify the field value of the record in MySQL, access the query page and find that the modified field value has changed. The log output by Hibernate is displayed, and the database returns the new field value.

This experiment shows that, at least in the case of InnoDB of mysql4.1, queries that do not use read-only transactions will not be able to read the data update value. Read-only transactions must be used to ensure data consistency of read records. This result surprised me very much, just like I expected.

I will try it on the Oracle platform.

BTW: If the MySQL table type is changed to MyISAM, no read data inconsistency even if no transaction is set.

Oracle has two ways to ensure transaction-level read consistency)

First, SET transaction isolation level serializable is used,
When you execute this command and read the data, duplicate copies are generated. You can also modify the data. However, when a large amount of data is modified, deadlock or exceptions may occur, use commit or rollback to set the isolation level back to the default mode Read committed,

Second, set transcation read only
When you execute this command, the database will generate a snapshot latch, which will consume some resources. If you want to modify the data, it will cause exceptions. using commit or rollback will release latch and set isolation level to the default mode Read committed,