Oracle - what statements need to be committed?

sql

transaction-management

database-locks

commit-rollback

byAlex Kataev·Jan 9, 2025

/* Remember this like your momma's apple pie recipe! */
/* DMLs like INSERT, UPDATE, DELETE: Commit before you split! */
/* DDLs like CREATE, ALTER, DROP: They auto-commit, just stop! */
/* SELECT: No changes, no commit, you get it? */

INSERT INTO my_table (column1) VALUES ('new_value');
COMMIT;  -- "I save UPDATES like a squirrel saves acorns for winter."

The "Commit" Primer: A deeper probe

Let's crack this open further:

How `COMMIT;` Functions and Why It's Needed

In Oracle, once you've unleashed an UPDATE, INSERT, DELETE, or MERGE command, a lock is firmed up on the affected data. This means no other transaction can tinker with it until you decide to use COMMIT; or ROLLBACK;. So in essence, COMMIT; is your magic word to unlock the data and make the changes stick.

`CALL` and `LOCK TABLE`: The Godfather of SQL Commands

Unlike your traditional DML commands, CALL is a little special - it's essentially a procedural call that might have DML operations lurking beneath the surface and hence, requires an COMMIT;. Similarly, LOCK TABLE is like this out-of-the-league command that won't release the locks unless explicitly asked to COMMIT; or ROLLBACK;.

The Autobots of Oracle SQL: DDL's Implicit Commits

On executing DDL statements like TRUNCATE, CREATE INDEX, and structured type alterations, Oracle SQL internally triggers an COMMIT;, thus releasing the locks and keeping everything ship-shape.

The Perils of Forgetting `COMMIT;`

Failing to pop that COMMIT; when needed can mean dark clouds on your transaction's horizon!

A Breach in Data Integrity

Data consistency can take a hit if changes aren't committed as they should be.

Concurrency Issues

Those nasty transaction locks can breed congestion and potential timeouts or even worse, deadlocks.

Error ORA-01555 and ORA-01002

Oh, have you met ORA-01555, the Snapshot Too Old error? He's a rude guest at the Oracle SQL error party and is often a spawn of mismanaged transactions. His cranky cousin, ORA-01002, is another one to watch out for.

Recipes to Follow When Using `COMMIT;`

Here's a sure-shot recipe to get your COMMIT; right, every time!

Handling Transaction Sizes

Like momma's Sunday roast, keep your transactions small and juicy for optimal performance.

Timing your `COMMIT;`

COMMIT; is a bit like a secret sauce, you don't want to overdo it. But you also can't skimp on it. Balance is key.

Error Handling

Do not underestimate the power of good exception handling - always ensure that transactions are either committed or appropriately rolled back for a flawless execution.

explain-codes / Sql / Oracle - what statements need to be committed?

Linked

How to test an SQL Update statement before running it?



Is there a way to SELECT and UPDATE rows at the same time?



Solutions for INSERT OR UPDATE on SQL Server



Mysql : Transaction within a procedure



If EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

