Consequences of locking a table that is acted upon via triggers?

Let's say I have two tables, t1 and t2. t1 has a trigger upon it so that on every INSERT an INSERT then happens on t2.

If I then acquire a write lock on 开发者_JS百科t2, what happens when I INSERT into t1? There are a number of things I'm curious about here:

1. Can I still INSERT into t1? Or will it lock?

2. If I can INSERT into t1 just fine, does that mean that the trigger will be queued up, just waiting until I UNLOCK t2?

3. If I'm INSERTing many things into t1 while I still have the lock on t2, are there any consequences to DB performance overall? Will this keep connections open or anything like that?

1. t1 will be not be locked implicitly, as the docs for implicit locking explain would be the case if you were doing the reverse (locking t1 will lock t2
2. A MySQL Error (ERROR 1100 (HY000): Table t1 was not locked with LOCK TABLES) will be generated on attempting to insert into t1 because it has not been locked, but the destination of its trigger has.
3. N/A, the query to write to t1 is not permitted by the MySQL engine.

Please consider your use case here however. If you are simply copying INSERTS into another table, and not calculating something, you might want to give a thought to using replication instead.

Verified on MySQL 5.1 with the following SQL of a toy action log:

DROP TABLE IF EXISTS acts, actions;

CREATE TABLE acts
(
  act_id int unsigned not null auto_increment primary key,
  user_id int unsigned not null
)
ENGINE=innodb;

CREATE TABLE actions
(
  action_id int unsigned not null auto_increment primary key,
  act_id int unsigned not null,
  user_id int unsigned not null
)
ENGINE=innodb;

DELIMITER #

CREATE TRIGGER acts_post_ins_trigger AFTER INSERT ON acts
FOR EACH ROW
BEGIN
  INSERT INTO actions(act_id, user_id) VALUES (new.act_id, new.user_id);
END#

DELIMITER ;

INSERT INTO acts VALUES(1,2);

SELECT * FROM acts;
SELECT * FROM actions;

LOCK TABLE actions WRITE;

SELECT('trying to SELECT acts w/ actions locked...') AS 'SELECT TEST';
SELECT * FROM acts;
SELECT('trying to INSERT into acts w/ actions locked...') AS 'INSERT TEST';
INSERT INTO acts VALUES(3,4);

UNLOCK TABLES;