Then, for each row in that table_a the trigger is fired calling that procedure which has autonomous transactions, so that you will have 5 transactions willing to update the same rows in table_a. So, you would have a history of that value and you would just insert into that table, so as to avoid updates which lead to deadlocks.ġ) Suppose a session updates some 5 rows in that table_a.
Val_time - date - indicating the date when that value was inserted Table_a_pk_col - holding the primary key for table_a and with foreign key to table_a Then, the new table will have the following columns: Suppose your table the trigger is on is called table_a and it has a primary key table_a_pk_col. And why do you need two different columns, one holding new_value and another one holding -new_value? In my mind one column would suffice. That would not require pragma autonomous_transaction. That is: the first row for which the trigger fires runs that job, while the other rows will not run that job, seeing that it already exists.Ī good solution would be a change in architecture, so as to have that so-called new_value stored in another table. You may have only one job with a given name, so that, if that job already exists, then, do nothing. Or, in the after update trigger launch a dbms_n_job with autodrop that will do what you want to have done. Not sure, having in view that you did not give the table structure (including primary key and indexes) and all the trigger code. Possibly calling the procedure in an after update trigger that is not for each row. This indicates you must review the whole application logic. In an on update trigger for each row on table_a you call a procedure with pragma automonous_transaction that updates the same table_a? Right? And then it so happens that the autonomous transaction gets to a deadlock with some other transaction, possibly yet another call of the same procedure that applies to another row in the same table_a. So, let me understand what you are actually doing. 1.7K Training / Learning / Certification.165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).3.8K Java and JavaScript in the Database.
Pini is an Oracle Database Certified Professional and also OPN Certified Specialist. He is the Product Manager for Databases Solutions at Quest Software. During these years Pini has worked as an Oracle DBA and Oracle DBA team leader. Pini Dibask is an Oracle Database Technologist and Architect with more than 10 years of experience. The session that its statement is being rolled back, will encounter an “ORA-00060: Deadlock detected while waiting for resource.” error message (that will also be recorded in the alert log file). The way Oracle handles deadlock scenarios is not by terminating one of the sessions or performing a transaction-level rollback, it's actually just by performing a statement-level rollback to one of the sessions. So how Oracle handles deadlock scenarios? SQL> UPDATE employee SET first_name = 'John' WHERE employee_id = 151 Īt this stage both sessions (session #1 and session #2) are blocked and waiting to each other’s locked resources - that's exactly what deadlock is.