How To Experiment a Data Lock in Oracle?

If you want to have some experience with data locks, you can create two windows running two SQL*Plus sessions. In session 1, you can run a UPDATE statements to create a data lock. Before committing session 2, switch to session 2, and run a UPDATE statements on the same row as session 1. The UPDATE statement will be put into wait status because of the data lock. Follow the tutorial exercise below to experience yourself:

(session 1)
SQL> connect HR/fyicenter

SQL> SET TRANSACTION 
  ISOLATION LEVEL 
  READ COMMITTED;
Transaction set.

SQL> SELECT * FROM fyi_links;
 ID URL              NOTES   
--- ---------------- --------
101 FYICENTER.COM            
110 CENTERFYI.COM            

SQL> UPDATE fyi_links 
  SET url='fyicenter.com' 
  WHERE id=101;
1 row updated.
(lock created on row id=101)

                                   (session 2)
                                   SQL> connect HR/fyicenter
                                   
                                   SQL> SET TRANSACTION 
                                     ISOLATION LEVEL 
                                     READ COMMITTED;
                                   Transaction set.

                                   SQL> UPDATE fyi_links SET
                                     notes='FAQ Resource' 
                                     WHERE id=101;
                                   (wait on lock at id=101)

SQL> COMMIT;
(lock on row id=101 released)
                                   (ready to run UPDATE)
                                   1 row updated.

SQL> SELECT * FROM fyi_links;
 ID URL              NOTES
--- ---------------- --------
101 fyicenter.com
110 CENTERFYI.COM

                                   SQL> COMMIT;

SQL> SELECT * FROM fyi_links;
 ID URL              NOTES
--- ---------------- ------------
101 fyicenter.com    FAQ Resource
110 CENTERFYI.COM

 

⇒Understanding SQL Transaction Management in Oracle

⇒⇒Oracle Database Tutorials