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, 1566🔥, 0💬
Popular Posts:
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
How to put statements into a statement block in SQL Server Transact-SQL? You can put statements into...
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...