Description:
Currently, any statement inside a trigger that attempts to get an exclusive lock on a record in the same table (i.e. SELECT...FOR UPDATE), or tries to modify records in the same table (i.e. CREATE TRIGGER AFTER DELETE...UPDATE my_table SET...) will greet the user with the error "Can't update table 'my_table' in stored function/trigger because it is already used by statement which invoked this stored function/trigger"
I understand that this was put in place because, theoretically, someone could create an on-insert trigger that inserted, or the like, and cause looping that could be destructive. However, the chance of someone doing something ridiculous like that shouldn't be a reason to deny use globally. Perhaps a new variable that allowed it to some extent, like the stored-proc recursion depth (though I'd be fine with just an on/off boolean) - that would keep people safe from themselves but still allow unique designs when the risks are understood.
I'd just like to be able to have a trigger exclusively lock records to guarantee a transactionally consistent value for itself. Or to be able to automate cleanup of old data on deletes. Or anything like that, which has no chance of recursion, but is allowed due to this global ban on it.
How to repeat:
Simple example, multi-col increments via triggers:
CREATE TABLE `my_table` (
`id1` int(10) unsigned NOT NULL,
`id2` int(10) unsigned NOT NULL,
PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB;
CREATE TRIGGER `ins_id2` BEFORE INSERT ON `my_table` FOR EACH ROW
set NEW.id2=ifnull((select max(ai2.id2)+1 from ai2 where ai2.id1=NEW.id1 for update),1);
insert into my_table set id1=1;
Suggested fix:
Allow this and document the risks of poor design, or create a variable that would disallow it by default but that we could enable knowing we won't cause recursion.
© 2024, Oracle Corporation and/or its affiliates