Why Do I Get an Open Transaction When Just Selecting from a Database View

Why do I get an open transaction when just selecting from a database View?

Any SQL Statement starts a transaction in Oracle.

From the manual:

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued. [...] An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements

Most probably those who are not seing this are running in auto-commit mode where the transaction started by a statement is immediately committed after the statement has finished.

Others have claimed that a SELECT is not DML, but again the manual clearly states:

Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:

   * Retrieve or fetch data from one or more tables or views (SELECT)

   * Add new rows of data into a table or view (INSERT)

[...]

Should we open transaction for a simple select queries?

No. If its a very simple SELECT query then there is no need to open and commit the transaction. As its just read only data.

Example

public long findOrderItemCount() {
    Session session = getSession();
    Query query = session.createQuery("select count(*) from OrderItem ");
    long count = (long)query.list().get(0);
    session.close();
    return count;
}

Use transactions for select statements?

In a highly concurrent application it could (theoretically) happen that data you've read in the first select is modified before the other selects are executed.

If that is a situation that could occur in your application you should use a transaction to wrap your selects. Make sure you pick the correct isolation level though, not all transaction types guarantee consistent reads.

Update :
You may also find this article on concurrent update/insert solutions (aka upsert) interesting. It puts several common methods of upsert to the test to see what method actually guarantees data is not modified between a select and the next statement. The results are, well, shocking I'd say.

Is insert query execute without opening transaction?

We use BEGIN TRAN in DELETE or UPDATE statement to make sure that our statement is correct and We get the correct number of results returned.

some developers doesn't use it in session or in batches , because they already try their statement and exactly know what it will do.
I advise you to visit this URL , It's really useful:

https://www.mssqltips.com/sqlservertutorial/3305/what-does-begin-tran-rollback-tran-and-commit-tran-mean/

What does a transaction around a single statement do?

It does nothing. All individual SQL Statements, (with rare exceptions like Bulk Inserts with No Log, or Truncate Table) are automaticaly "In a Transaction" whether you explicitly say so or not.. (even if they insert, update, or delete millions of rows).

EDIT: based on @Phillip's comment below... In current versions of SQL Server, Even Bulk Inserts and Truncate Table do write some data to the transaction log, although not as much as other operations do. The critical distinction from a transactional perspective, is that in these other types of operations, the data in your database tables being modified is not in the log in a state that allows it to be rolled back.

All this means is that the changes the statement makes to data in the database are logged to the transaction log so that they can be undone if the operation fails.

The only function that the "Begin Transaction", "Commit Transaction" and "RollBack Transaction" commands provide is to allow you to put two or more individual SQL statements into the same transaction.

EDIT: (to reinforce marks comment...) YES, this could be attributed to "superstitious" programming, or it could be an indication of a fundamental misunderstanding of the nature of database transactions. A more charitable interpretation is that it is simply the result of an over-application of consistency which is inappropriate and yet another example of Emersons euphemism that:

A foolish consistency is the hobgoblin of little minds,

adored by little statesmen and philosophers and divines

Do you need a database transaction for reading data?

All database statements are executed within the context of a physical transaction, even when we don’t explicitly declare transaction boundaries (BEGIN/COMMIT/ROLLBACK).

If you don't declare transaction boundaries, then each statement will have to be executed in a separate transaction (autocommit mode). This may even lead to opening and closing one connection per statement unless your environment can deal with connection-per-thread binding.

Declaring a service as @Transactional will give you one connection for the whole transaction duration, and all statements will use that single isolation connection. This is way better than not using explicit transactions in the first place.

On large applications, you may have many concurrent requests, and reducing database connection acquisition request rate will definitely improve your overall application performance.

JPA doesn't enforce transactions on read operations. Only writes end up throwing a TransactionRequiredException in case you forget to start a transactional context. Nevertheless, it's always better to declare transaction boundaries even for read-only transactions (in Spring @Transactional allows you to mark read-only transactions, which has a great performance benefit).

Now, if you use declarative transaction boundaries (e.g. @Transactional), you need to make sure that the database connection acquisition is delayed until there is a JDBC statement to be executed. In JTA, this is the default behavior. When using RESOURCE_LOCAL, you need to set the hibernate.connection.provider_disables_autocommit configuration property and make sure that the underlying connection pool is set to disable the auto-commit mode.

Should there be a Transaction for Read Queries?

So you get a consistent view of the database. Imagine you have two tables that link to each other, but for some reason you do 2 selects... in pseuodocode:

myRows = query(SELECT * FROM A)
moreRows = query(SELECT * FROM B WHERE a_id IN myRows[id])

If between the two queries, someone changes B to delete some rows, you're going to have a problem.

Is there a difference between a SELECT statement inside a transaction and one that is outside of it?

Yes, the one inside the transaction can see changes made by other previous Insert/Update/delete statements in that transaction; a Select statement outside the transaction cannot.

If all you are asking about is what the Isolation Level does, then understand that all Select statements (hey, all statements of any kind) - are in a transaction. The only difference between one that is explicitly in a transaction and one that is standing on its own is that the one that is standing alone starts its transaction immediately before it executes it, and commits or roll back immediately after it executes;

whereas the one that is explicitly in a transaction can (because it has a Begin Transaction statement) can have other statements (inserts/updates/deletes, whatever) occurring within that same transaction, either before or after that Select statement.

So whatever the isolation level is set to, both selects (inside or outside an explicit transaction) will nevertheless be in a transaction which is operating at that isolation level.

Addition:
The following is for SQL Server, but all databases MUST work in the same way. In SQL Server the Query Processor is always in one of 3 Transaction Modes, AutoCommit, Implicit, or Explicit.

• AutoCommit is the default transaction management mode of the SQL Server Database Engine. .. Every Transact-SQL statement is committed or rolled back when it completes. ... If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. This is the default, and is the answer to @Alex's question in the comments.

• In Implicit Transaction mode, "... the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. Implicit transaction mode generates a continuous chain of transactions. ..." Note that the italicized snippet is for each transaction, whether it be a single or multiple statement transaction.

• The engine is placed in Explicit Transaction mode when you explicitly initiate a transaction with BEGIN TRANSACTION Statement. Then, every statement is executed within that transaction until you explicitly terminate the transaction (with COMMIT or ROLLBACK) or if a failure occurs that causes the engine to terminate and Rollback.

---

Related Topics