CODEWITHSUNDEEP
mysqlsqlstored-proceduresscheduled-tasks
Marcello Verona
Marcello Verona

Reputation: 540

Auto-Update a MySQL row exactly after n seconds after the insert

I have this table in MySQL:

CREATE TABLE `comp` (
  `id_comp` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_exam` int(11) unsigned NOT NULL,
  `date_start` datetime NOT NULL,
  `date_stop` datetime DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1' COMMENT '1=open, 0=closed, -1=forced',
  PRIMARY KEY (`id_comp`)
);

In another table (exam) I have the max_execution_time allowed, e.g. 600 seconds. After 600 seconds, if I have the status already open (1) I would update the row, change the status to -1 and fill the data_stop field (with CURRENT_DATETIME).

I already realized this with an external script (resque). Should I do with the DB itself, maybe with a stored procedure? It's possible with MySQL?

I tried with a stored procedure and a SLEEP(), but it blocks the execution (and the asynch execution seems not feasible). On other hand the EVENT syntax cannot accept parameters.

Any idea?

Thanks

p.s. Otherwise, can I do it with Postgresql?

Upvotes: 0

Views: 305

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If you just want the data to "look" correct, then you can use a view. The view can calculate the statuses on-the-fly:

create view v_comp as
    select c.id_comp, c.id_exam, c.date_start,
           (case when date_end is not null then date_end
                 when now() > c.date_start + interval e.max_execution_time second
                 then c.date_start + interval e.max_execution_time second
            end) as date_end,
           (case when date_end is not null then status
                 when now() > c.date_start + interval e.max_execution_time second
                 then -1
            end) as status
    from comp c join
         exams e
         on c.id_exam = e.id_exam;

The advantage of this approach is that the data always is correct. There is no problem with delays on the computer delaying things by a second or two -- or perhaps much longer.

You can actually update the data in a variety of ways:

  • A batch process, such as once per day or once per hour for rows that need to be updated.
  • You could use a queueing system, where the update is inserted into the queue. In MySQL, this would probably use the event scheduler.

Upvotes: 1

Related Questions