Commit and Rollback SQL Developer

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

(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.

COMMIT VS ROLLBACK

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.

(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;

SAVEPOINT

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.

[SAVEPOINT name;

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.

FOR UPDATE

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:

PRAGMA AUTONOMOUS_TRANSACTION;

More about SQL

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