CODEWITHSUNDEEP
mysqltriggersinnodbmyisamatomic
porton
porton

Reputation: 5805

MyISAM and triggers atomicity

Suppose I have some operation (let it be INSERT) on a MyISAM table with a trigger attached to it.

I know that MySQL does implicit table locks while running this INSERT.

Question: Will the trigger be run inside this implicit lock? Will the tables used in the trigger be locked by this lock?

In other words, is execution of the trigger a part of the atomic operation (such as my INSERT)?

BTW, what about InnoDB? the same? is the trigger wrapped into an explicit transaction?

Upvotes: 0

Views: 272

Answers (1)

Vatev
Vatev

Reputation: 7590

An InnoDB query runs in an implicit transaction.

It actually runs everything in a transaction. The "no transaction" mode is emulated by an implicit commit after every statement ("starting a transaction" disables the automatic implicit commit).

This transaction includes all triggers which run for the query, and also the shared mode locks for foreign keys.

With MyISAM the trigger will lock any table it modifies, just as a normal query will. Whether or not it is atomic - I don't know.

Upvotes: 1

Related Questions