Auto-commit mode means that when a statement is completed, the method commit is
called on that statement automatically. Auto-commit in effect makes every
SQL statement a transaction. The commit occurs when the statement completes
or the next statement is executed, whichever comes first. In the case of a
statement returning a
Some applications might prefer to work with
You should be aware of the following when you use auto-commit:
You cannot use auto-commit if you do any positioned updates or deletes (that is, an update or delete statement with a "WHERE CURRENT OF" clause) on cursors which have the close cursors on commit option set.
Auto-commit automatically closes cursors, which are explicitly opened with the close on commit option, when you do any in-place updates or deletes.
A cursor declared to be held across commit can execute updates and issue multiple commits before closing the cursor, but the cursor must be repositioned before any statement following the commit. If this is attempted with auto-commit on, an error is generated.
You cannot execute procedures within SQL statements if those procedures
perform a commit or rollback on the current connection. Since in the auto-commit
mode all SQL statements are implicitly committed,
Procedures that use nested connections are not permitted to turn auto-commit on or off or to commit or roll back.
When an application uses table-level locking and the SERIALIZABLE isolation level, all statements that access tables hold at least shared table locks. Shared locks prevent other transactions that update data from accessing the table. A transaction holds a lock on a table until the transaction commits. So even a SELECT statement holds a shared lock on a table until its connection commits and a new transaction begins.