Collections:
Experiments of Data Locks in Oracle
How To Experiment a Data Lock in Oracle?
✍: FYIcenter.com
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
⇒ Show Existing Locks on the Database in Oracle
⇐ How Data Locks Are Respected in Oracle
2019-08-08, 1670🔥, 0💬
Popular Posts:
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...