Solving the mutating table problem in Oracle SQL produces a deadlock?

Using an autonomous transaction for this sort of thing is almost certainly a mistake. What happens if the transaction that inserted the new thumbnail needs to rollback? You've already committed the change to the other rows in the table.

Using an autonomous transaction for this sort of thing is almost certainly a mistake. What happens if the transaction that inserted the new thumbnail needs to rollback? You've already committed the change to the other rows in the table.

If you want the data to be transactionally consistent, you would need multiple triggers and some way of storing state. The simplest option would be to create a package with a collection of thumbnail. Url%type then create three triggers on the table.

A before statement trigger would clear out the collection. A row-level trigger would insert the :new. Url value into the collection.An after statement trigger would then read the values from the collection and call the check_thumbnail_set_others procedure (which would not be an autonomous transaction).

1 +1 this is the standard method to solve the infamous Mutating Table problem. – Jeffrey Kemp Feb 28 at 4:02.

I still get the same problem :( - ORA-00060 deadlock detected. – Mat Feb 27 at 17:44 Is url the primary key? You may be updating too many records if it isn't.

Assuming the key on your table is called theId, change your update statement to UPDATE photograph SET thumbnail = 0 WHERE url p_url and theid p_id; – Sumit Feb 27 at 18:16 The URL is the primary key. It's for a gallery where only one image should be set as the thumbnail and I can't think of a better way of doing this - I've probably made a stupid mistake somewhere! – Mat Feb 27 at 18:22 Apparently it works as long as I commit after the update query to change the thumbnail!

Any way of making the query itself commit? – Mat Feb 27 at 18:40.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.