Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Experiments of Data Locks

By: FYIcenter.com

(Continued from previous topic...)

How To Experiment a Data Lock?

If you want to have some experience with data locks, you can create two windows runing 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

(Continued on next topic...)

  1. What Is a Transaction?
  2. How To Start a New Transaction?
  3. How To End the Current Transaction?
  4. How To Create a Test Table for Transaction Testing?
  5. How To Commit the Current Transaction?
  6. How To Rollback the Current Transaction?
  7. What Happens to the Current Transaction If a DDL Statement Is Executed?
  8. What Happens to the Current Transaction If the Session Is Ended?
  9. What Happens to the Current Transaction If the Session Is Killed?
  10. How Does Oracle Handle Read Consistency?
  11. What Is a READ WRITE Transaction?
  12. What Is a READ ONLY Transaction?
  13. How To Set a Transaction To Be READ ONLY?
  14. What Are the Restrictions in a READ ONLY Transaction?
  15. What Are the General Rules on Data Consistency?
  16. What Are Transaction Isolation Levels Supported by Oracle?
  17. What Is a Data Lock?
  18. How Data Locks Are Respected?
  19. How To Experiment a Data Lock?
  20. How To View Existing Locks on the Database?
  21. What Is a Dead Lock?
  22. How Oracle Handles Dead Locks?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...