Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following two tables in a MySQL database:

CREATE TABLE `names` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE `events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name_id` bigint(20) unsigned NOT NULL,
  `time_stamp` decimal(26,9) NOT NULL,
  `event` enum('start','add','init','update','disconnect','remove','stop') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_id_time_stamp` (`name_id`,`time_stamp`),
  KEY `time_stamp` (`time_stamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

I execute a sequence of INSERT...SELECT statements such as the following in a transaction:

INSERT INTO events (name_id, time_stamp, event)
SELECT id, '1403566344.858772100', 'init'
FROM names
WHERE name = 'H1:ASC-POP_B_WHITEN_TOGGLE_3';

The problem is that I can get a deadlock when I also execute a statement such as:

SELECT events.name_id, MAX(events.time_stamp) AS time_stamp
FROM events
WHERE events.time_stamp <=  '1403566344.856155250'
GROUP BY events.name_id;

It seems that this can be avoided if I order the sequence of inserts by name_id and time_stamp. The question is, how do I order the inserts by name_id when it is not known in the application? Do I have to execute separate queries to find these values first? I am hoping to avoid that, since they need to run quickly.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.