CODEWITHSUNDEEP
mysqltriggers
David
David

Reputation: 144

Are Triggers Based On Queries Atomic?

I have a table that has a Sequence number. This sequence number will change and referencing the auto number will not work. I fear that the values of the trigger will collide. If two transactions read at the same time.

I have ran simulated tests on 3 connections @ ~1 million records each and no collisions.

CREATE TABLE `aut` (
  `au_id` int(10) NOT NULL AUTO_INCREMENT,
  `au_control` int(10) DEFAULT NULL,
  `au_name` varchar(50) DEFAULT NULL,
  `did` int(10) DEFAULT NULL,
  PRIMARY KEY (`au_id`),
  KEY `Did` (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

TRIGGER `binc_control` BEFORE INSERT ON `aut` 
FOR EACH ROW BEGIN
SET NEW.AU_CONTROL = (SELECT COUNT(did)+1 FROM aut WHERE did = NEW.did);
END;

Upvotes: 4

Views: 2299

Answers (4)

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

The MySQL UUID_SHORT() function might be useful to you. It is atomic and generates an ever_incrementing BIGINT UNSIGNED value every time it is called, subject to the requirement that you don't stop the server, set the clock backwards in time to a time prior to or close to the previous start (depending on how often you call it) and start the server again, or change the value of @@server_id to a value whose LSB is lower.

http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_uuid-short

Upvotes: 0

brak2718
brak2718

Reputation: 84

Even within an explicit isolated transaction, there is still a race condition where you can end up with duplicate au_control values. count(*) is going to be horrible for performance. There are better ways to get a monotonically increasing guid if that is what you're trying to do.

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78473

Technically, yes. It's atomic, and the initial statement plus all side effects are committed as the same transaction.

But then, anything can go wrong with your query depending on the isolation level. MySQL has a cached value in the catalog if memory serves, but this cache can potentially become stale if you're reading it with non-serializable isolation in concurrent transactions.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562398

Yes, this is subject to race conditions if two sessions run the trigger at the same time. You shouldn't use this solution.

It may not happen during testing, but you can assume it will happen during production. :-)

There's an old saying, One in a million is next Tuesday.

Upvotes: 3

Related Questions