Bug #89245 Documentation bug on triggers
Submitted: 15 Jan 2018 14:09 Modified: 19 Jan 2018 15:55
Reporter: Victor Porton Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[15 Jan 2018 14:09] Victor Porton 
Description:
It is not clear in the documentation whether an operation (such as INSERT, UPDATE, etc.) are atomic TOGETHER with triggers (in such cases as a MyISAM DB).

I other words, does INSERT (or whatever) operation locks not only the table inserted into, but also all tables in the triggers, as a single atomic operation consisting of the main INSERT operation and all triggers.

Please update the documentation.

How to repeat:
Read the docs.
[15 Jan 2018 14:29] Victor Porton 
It also should be made clear, whether all trigger's row operations run in a single atomic operation.
[19 Jan 2018 14:08] MySQL Verification Team 
Hi,

I don't see why would that need to be explicitly added to the doc. Triggers are executed trough execution plan of the DML and are inside that transaction. Any fail in the trigger will fail the whole transaction, and any operation done by trigger is isolated from other transactions in the way defined by your transaction isolation settings. In order for all that to work, you of course have to use transaction capable storage engine.

kind regards
Bogdan
[19 Jan 2018 15:08] Victor Porton 
@Bogdan: I ask specifically for MyISAM. In MyISAM there are no transactions and thus your explanation fails.
[19 Jan 2018 15:55] MySQL Verification Team 
> I ask specifically for MyISAM.

Hi,

I was under impression I'm the last one on this Eearth to still use MyISAM :D ... apologies you mentioned "row operations" so I assumed InnoDB automatically, my mistake.

> In MyISAM there are no transactions

Exactly, and that's why there can not be any atomicity guaranteed, that's default answer and the only "guaranteed" one and it does not require any update to documentation (and since MyISAM is slowly being phased out of importance I doubt anyone cares enough). Now, IIRC we do lock all the tables first, then do the work and then unlock them after we are done so you do have atomicity, but again, without transactions - no guarantees.

Hope this helps
Bogdan