Werbung / Advertisements

Commit and Rollback SQL Developer

Commit Rollback SQLDEV
Werbung / Advertisements

The SQL Developer sets implicit automatic COMMITs. This COMMIT is subconsciously and automatically canceled in the following situations.

Werbung / Advertisements

(1) SQL Developer quits normally, with no COMMIT or ROLLBACK statement. In this case, a COMMIT is sent before the program is terminated. However, this does not happen in the event of a system failure of the PC or an abnormal termination of SQL Developer. Here is a ROLLBACK.

(2) Issuing a DDL (Data Definition Language) statement also results in an automatic COMMIT. DDL statements include CREATE, DROP, ALTER, TRUNCATE, COMMENT, and RENAME

(3) Issuing a DCL (Data Control Language) statement results in an automatic COMMIT. DCL statements include GRANT and REVOKE.

Current Posts

  • Bus connections and buses on Corfu
    Werbung / Advertisements Corfu is one of the most beautiful and popular Greek islands, attracting visitors with an impressive mix of picturesque beaches, rich history and vibrant culture. To fully explore the island’s diversity,...
  • Rhodes Beaches and Rhodes Snorkeling
    Werbung / Advertisements Rhodes, the largest of the Dodecanese islands in Greece’s Aegean Sea, is famous for its rich history, archaeological sites and, of course, stunning beaches. The island is also a paradise for...
  • The Skiathos Bus System: A Comprehensive Guide
    Werbung / Advertisements Hello, travel enthusiasts! Today, we are taking you on a different kind of journey by giving you a deep insight into the bus system of Skiathos, the backbone of public transport...


Werbung / Advertisements

A COMMIT saves the data changes to the database, overwriting the state of the previous data. From this point on, all users can see the results. In addition, the lock of affected cells is lifted. This is consistent with “write only wait writes” (see Read Consistency). In addition, all SAVEPOINTS are deleted.

A ROLLBACK, on ​​the other hand, discards any unsaved changes, making the previous state using the UNDO tablespace. In addition, the affected locked lines are released again.

If individual DML (Data Manipulation Language) statements fail, only that statement will be rolled back. This is done with an implicit SAVEPOINT. This is how an INSERT is implemented as follows.

Werbung / Advertisements

(1) SAVEPOINT before_insert;

(2) INSERT INTO <schema>. <Table> VALUES (…)

(3a) no error: RELEASE SAVEPOINT (only possible in MySQL).

(3b) Error: ROLLBACK TO SAVEPOINT before_insert;


A SAVEPOINT can only be released with a COMMIT or ROLLBACK in ORACLE. Otherwise the SAVEPOINT remains. SAVEPOINTS can be overridden simply by creating the same SAVEPOINT again.

Werbung / Advertisements

The reading consistency ensures a consistent, constant data view. The following rules apply:

– Writes wait for writes

– Reads do not wait for writes. Locked cells are taken from the UNDO tablespace

– Writes do not wait for read operations.

– Read operations also do not wait for read operations.

Werbung / Advertisements


Another possibility is to block the affected cells in advance. This is done with the FOR UPDATE statement. If the affected cells are already locked by another user, the database waits to execute the SELECT statement. The lock can only be canceled by a COMMIT or ROLLBACK.

An example:

SELECT emp_name FROM emp WHERE empno = 12345 FOR UPDATE;

The FOR UPDATE statement locks all affected rows of one or more tables. More specifically, there may be a FOR UPDATE OF <column name> which only locks the affected value of the columns. This allows other tables (e.g., which are needed by JOINS but will not be updated) to be left unlocked to other users.

SELECT emp_name FROM emp JOIN dept USING (deptid) WHERE deptname = 'xyz' FOR UPDATE of emp_name;

The above example does not block the debt table. Also in the table emp, all other fields are not locked and can be updated. This can be tested by autonomous transactions:


More about SQL

You can find a lot of more SQL stuff under the SQL section.

You may also like...

Werbung / Advertisements