Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Simulating deadlock

synozureMay 15 2020 — edited May 17 2020

Hi,

I am experimenting with deadlocks and need to simulate one.

I have opened up a couple of SQLcl clients and connected to a database. I was following step-by-step instructions in our examples to show a deadlock however I do not get the expected results.

Client 1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Client 2:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Client 1:

UPDATE DEPARTMENT

SET BUDGET = 500

WHERE DNAME = 'SALES';

Client 2:

UPDATE DEPARTMENT

SET BUDGET = 600

WHERE DNAME = 'IT';

Client 1:

UPDATE DEPARTMENT

SET BUDGET = 550

WHERE DNAME = 'SALES';

It says here "As expected, the first transaction is blocked." However mine updates successfully

Client 2:

UPDATE DEPARTMENT

SET BUDGET = 650

WHERE DNAME = 'IT';

And here it says the system should detect a deadlock and output:

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

I can get both clients to hang but it never outputs a message regarding a deadlock and they're both frozen until I close the windows. How can I get it to detect a deadlock with from update statements and abort like the example says it should?

Comments
Post Details
Added on May 15 2020
4 comments
1,920 views