CODEWITHSUNDEEP
mysqltransactionsinsertinnodbmyisam
user1023768
user1023768

Reputation: 13

Mysql insert row ignoring current transaction

I have a MySQL table implementing a mail queue, and I use it also to send mails which reports unexpected errors in the system. Sometimes these unexcepted errors ocurrs inside a transaction so when I rollback the transacion also I undo the row inserted (the mail which is reporting the unexpected error) in the mail queue table.

My question is how can I force to insert a row in a table in the middle a transaction ignoring the possible transaction rollback?. I mean, If the transactions finally rollsback, not to rollback also the row insertion for the email reporting the error details.

This table can be read by multiple asyncronous process to send the mails in the queue, so in this scenario the rows have to be blocked to send only once the emails so is not possible to use a MyISAM table type and is using Innodb.

Thanks in advance.

Upvotes: 1

Views: 1129

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65274

If you INSERT should survive a ROLLBACK of the transaction, it is safe to say, that it is not part of the transaction. So what you should do is to simply move it outside the transaction. There are many ways to achieve that:

  • While in the transaction, instead of running you INSERT, store the fields in session variables (these will survive a ROLLBACK), after the transaction run the insert from the session variables
  • Rethink your schema - this reeks of some deeper-lying problem
  • Open a second DB connection and run your INSERT on this one, it will not be affected by the transaction on the first connection.

Upvotes: 3

Stelian Matei
Stelian Matei

Reputation: 11623

You could create a different connection to the database to insert the errors and it won't be in the same transaction context, so they would be inserted.

Upvotes: 1

Related Questions