Oracle Tips and Tricks — David Fitzjarrell

December 14, 2007

Having problems with commitment?

Filed under: General — dfitzjarrell @ 16:25

When to commit in a series of transactions is an oft-asked question, the answer to which depends upon what the user has done, is doing and plans on doing after the data changes are made. So, which operations commit automatically and which ones don’t? Obviously any DML requires a commit. Unless it’s followed by DDL. DDL issues commits automagically, both before the DDL starts and after the DDL completes. Now that we have that concept clear as mud, let’s look at an example:

SQL> --
SQL> -- Create test table
SQL> --
SQL> create table commit_test(my_id number, 
2 my_data varchar2(40),
3 my_color varchar2(12),
4 constraint commit_test_pk
5 primary key(my_id)
6 );

Table created.

SQL> 
SQL> --
SQL> -- Load it up
SQL> --
SQL> insert all
2 into commit_test
3 values (1, 'Lollie''s knickers are','blue')
4 into commit_test
5 values (2, 'Lollie''s knickers are','pink')
6 into commit_test
7 values (3, 'Lollie''s knickers are','orange')
8 into commit_test
9 values (4, 'Lollie''s knickers are','blue')
10 into commit_test
11 values (5, 'Lollie''s knickers are','blue')
12 into commit_test
13 values (6, 'Lollie''s knickers are','orange')
14 into commit_test
15 values (7, 'Lollie''s knickers are','blue')
16 into commit_test
17 values (8, 'Lollie''s knickers are','chartreuse')
18 into commit_test
19 values (9, 'Lollie''s knickers are','blue')
20 select * from dual;

9 rows created.

SQL> 
SQL> --
SQL> -- Commit (we want to do this)
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Check table contents
SQL> --
SQL> select *
2 from commit_test;

MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are blue
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are blue
5 Lollie's knickers are blue
6 Lollie's knickers are orange
7 Lollie's knickers are blue
8 Lollie's knickers are chartreuse
9 Lollie's knickers are blue

9 rows selected.

SQL>
SQL> --
SQL> -- Make a 'temporary' update
SQL> --
SQL> update commit_test
2 set my_color = 'cinnamon'
3 where my_color = 'blue'
4 /

5 rows updated.

SQL>
SQL> --
SQL> -- Check table contents
SQL> --
SQL> select *
2 from commit_test;

MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are cinnamon
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are cinnamon
5 Lollie's knickers are cinnamon
6 Lollie's knickers are orange
7 Lollie's knickers are cinnamon
8 Lollie's knickers are chartreuse
9 Lollie's knickers are cinnamon

9 rows selected.

SQL>
SQL> --
SQL> -- We SHOULD rollback here, but we
SQL> -- execute a CTAS instead
SQL> --
SQL> create table commit_test_save
2 as select * from commit_test;

Table created.

SQL>
SQL> --
SQL> -- Check original table contents
SQL> --
SQL> -- Ooops! We committed our changes
SQL> --
SQL> select * 
2 from commit_test;

MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are cinnamon
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are cinnamon
5 Lollie's knickers are cinnamon
6 Lollie's knickers are orange
7 Lollie's knickers are cinnamon
8 Lollie's knickers are chartreuse
9 Lollie's knickers are cinnamon

9 rows selected.

Well, gee, our temporary changes were made permanent by the DDL creating our table of saved data. Let’s try this again, and rollback our changes before we try to create that second table:



SQL>
SQL> --
SQL> -- Truncate original table, drop
SQL> -- table with saved data
SQL> --
SQL> truncate table commit_test;

Table truncated.

SQL> drop table commit_test_save;

Table dropped.

SQL>
SQL> --
SQL> -- Load up original table again
SQL> --
SQL> insert all
2 into commit_test
3 values (1, 'Lollie''s knickers are','blue')
4 into commit_test
5 values (2, 'Lollie''s knickers are','pink')
6 into commit_test
7 values (3, 'Lollie''s knickers are','orange')
8 into commit_test
9 values (4, 'Lollie''s knickers are','blue')
10 into commit_test
11 values (5, 'Lollie''s knickers are','blue')
12 into commit_test
13 values (6, 'Lollie''s knickers are','orange')
14 into commit_test
15 values (7, 'Lollie''s knickers are','blue')
16 into commit_test
17 values (8, 'Lollie''s knickers are','chartreuse')
18 into commit_test
19 values (9, 'Lollie''s knickers are','blue')
20 select * from dual;

9 rows created.

SQL>
SQL> --
SQL> -- Commit (we want to do this)
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Check table contents
SQL> --
SQL> select *
2 from commit_test;

MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are blue
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are blue
5 Lollie's knickers are blue
6 Lollie's knickers are orange
7 Lollie's knickers are blue
8 Lollie's knickers are chartreuse
9 Lollie's knickers are blue

9 rows selected.

SQL>
SQL> --
SQL> -- Make a 'temporary' update
SQL> --
SQL> update commit_test
2 set my_color = 'cinnamon'
3 where my_color = 'blue'
4 /

5 rows updated.

SQL>
SQL> --
SQL> -- Check table contents
SQL> --
SQL> select *
2 from commit_test;

MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are cinnamon
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are cinnamon
5 Lollie's knickers are cinnamon
6 Lollie's knickers are orange
7 Lollie's knickers are cinnamon
8 Lollie's knickers are chartreuse
9 Lollie's knickers are cinnamon

9 rows selected.

SQL>
SQL> --
SQL> -- We rollback here
SQL> --
SQL> rollback;

Rollback complete.

SQL>
SQL> --
SQL> -- NOW we save the original table
SQL> -- data
SQL> --
SQL> create table commit_test_save
2 as select * from commit_test;

Table created.

SQL>
SQL> --
SQL> -- Check original table contents
SQL> --
SQL> select *
2 from commit_test;

MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are blue
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are blue
5 Lollie's knickers are blue
6 Lollie's knickers are orange
7 Lollie's knickers are blue
8 Lollie's knickers are chartreuse
9 Lollie's knickers are blue

9 rows selected.

SQL>
SQL> --
SQL> -- Check saved data contents
SQL> --
SQL> select *
2 from commit_test_save;

MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are blue
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are blue
5 Lollie's knickers are blue
6 Lollie's knickers are orange
7 Lollie's knickers are blue
8 Lollie's knickers are chartreuse
9 Lollie's knickers are blue

9 rows selected.

SQL>

Now we have the original source data saved. Of course I’d have started off with the CTAS, but that wouldn’t have illustrated the concept of DDL implicit commits.

Yet another way to commit without committing is to simply exit your SQL*Plus session. A garden-variety exit is actually an ‘exit commit’, so any work you’ve done prior to the exit will be committed whether you wanted it to or not. A little-known option to exit is:

SQL> exit rollback

which will, miracle of miracles, rollback your pending work to the last commit. And it works, too:

SQL> create table mytest (mykey number,
2 myval varchar2(40),
3 constraint mytest_pk
4 primary key(mykey)
5 );

Table created.

SQL>
SQL> insert all
2 into mytest
3 values (1, 'Test 1')
4 into mytest
5 values (2, 'Test 2')
6 into mytest
7 values (3, 'Test 3')
8 into mytest
9 values (4, 'Test 4')
10 into mytest
11 values (5, 'Test 5')
12 select * 13 from dual;

5 rows created.

SQL>
SQL> select * from mytest;

MYKEY MYVAL
---------- ----------------------------------------
1 Test 1
2 Test 2
3 Test 3
4 Test 4
5 Test 5

SQL>
SQL> exit rollback
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Data Mining option

C:\sql\oraoft01\examples>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 14 11:49:17 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect **********
Connected.
SQL> select * from mytest;

no rows selected

SQL>

So you’ve seen two ways where commits can be executed without ever coding them. Which can be a blessing, or a curse, depending upon your situation.

Of course normally you’d want to commit your changes, so in those cases it’s best to simply submit a commit and let Oracle take care of the rest. When would you not want to commit? Inside a PL/SQL loop is one time, but that I’ll leave for another discussion.

Basically, if you’re changing data, commit. That way you’re not surprised later.

Blog at WordPress.com.