In this post we’ll do a short discussion about Oracle transactions, and a simple demonstration of the principles discussed.
The term transaction in Oracle means to take some data in the database from one consistent state to another. The work done in a transaction by one user is not visible to any other users until that transaction is committed. Committing a transaction marks its completion and makes the changes permanent and visible to all database users. Conversely, if I do not commit, but instead rollback a transaction, I undo its changes and return the data to its state at the beginning of the transaction.
Let’s set up a simple table.
U1> create table t1 (c1 number); Table created. U1> insert into t1 values (1); 1 row created. U1> commit; Commit complete. U1> select * from t1; C1 ---------- 1
Now, in another session:
U2> select * from u1.t1; C1 ---------- 1
Back in the first session:
U1> update t1 set c1 = 2; 1 row updated. U1> select * from t1; C1 ---------- 2
Second session:
U2> select * from u1.t1; C1 ---------- 1
The first session has not committed. This transaction is still in progress, so the second session still sees the data in its unchanged state.
We can confirm this transaction is in progress by looking at the dynamic performance view V$TRANSACTION and joining it to V$SESSION with the sid of the session making the updates:
U1> select distinct sid from v$mystat; SID ---------- 91 U1> select t.start_time 2 from v$transaction t, v$session s 3 where t.addr = s.taddr 4 and s.sid = 91; START_TIME -------------------- 05/07/16 15:18:15
We can also see that user U1 has obtained a lock on the table T1:
U1> select object_id 2 from v$locked_object; OBJECT_ID ---------- 20090 U1> select owner, object_name from dba_objects 2 where object_id=20090; OWNER OBJECT_NAME ------------------------------ ------------------------------ U1 T1
Looking at v$lock we can see the specific locks that are held:
U1> select type, lmode 2 from v$lock 3 where sid = 91; TY LMODE -- ---------- AE 4 TM 3 TX 6
AE locks have to do with Oracle Editions, which is outside the scope of this post.
The TM lock, mode 3 (subexclusive lock), prevents the object itself from being modified (dropped, columns added, etc) while the transaction is in progress. However, queries and DML on other rows is permitted.
The TX lock, mode 6 (exclusive), is a lock on the specific rows being updated. Any other user attempting to update this same row will see their session hang until the original transaction is committed or rolled back.
We commit, and the locks are released:
U1> commit; Commit complete. U1> select type, lmode 2 from v$lock 3 where sid = 91; TY LMODE -- ---------- AE 4
The other user now sees the modified data as the changes have been made permanent:
U2> select * from u1.t1;
C1
----------
2
One thing to keep in mind – if you make ten updates to ten different tables, or a hundred—until you commit or rollback, all these actions are contained in a single transaction from Oracle’s perspective.
In the next post we’ll talk about the mechanisms that allowed Oracle to show user U2 the data as it still was despite the changes being made by user U1.
Recent Comments