views:

158

answers:

1

Out of habit I've been using try/catch blocks in my application code for all SQL queries, with a rollback at the beginning of the catch block. I've also been committing those which are successful. Is this necessary for SELECTs? Does it free up something on the database side? The select statements aren't altering any data so it seems somewhat pointless, but perhaps there is some reason I'm not aware of.

e.g.

try {
  $results = oci_execute($statement)
  oci_commit($connection);
  return $results;
}
catch {
  oci_rollback($connection)
  throw new SqlException("failed");
}
+6  A: 

SELECT statements in Oracle (unless they are SELECT FOR UPDATE) never lock any records and never open transactions implicitly.

Unless you issued any DML operations within your transaction, it will not matter whether you commit or roll back your transaction.

Quassnoi
@Quassnoi: Well, it can. If one opens a cursor, one will hold rollback (UNDO) for every transaction that was in flight since the cursor was opened. So if one is opening any recordsets, one does need to close them when done in order to help the poor DBA's avoid the dreaded ORA-01555.In some database systems (I'm looking at you, DB2) you do need to commit after select to eliminate read locks. That's probably where the coding standard evolved from, or just straight paranoia.
Adam Musch
@Adam paranoia in my case
RenderIn
@Adam: the `UNDO` is generated by the other transactions, not the one that keeps the cursor (we assume it's read only). The other transactions may alter the data, modify the data pages (in which case the `UNDO` will be generated and held until the end of the transaction) and commit (in which case the `UNDO` is released and marked as free to rewrite). In a read-only cursor, `ORA-01555` may only be generated if the data required by the cursor was overwritten by a commited concurrent transaction. Again, the cursor itself will not lock the data, generate any `UNDO` or even lock `UNDO` it needs.
Quassnoi