mysql - transacciones - ¿Cómo se bloquean las tablas innodb cuando se procesa el disparador ON INSERT?

transacciones en mysql workbench

Question

Tengo dos tablas innodb:

artículos

id     | title    | sum_votes
------------------------------
1      | art 1    | 5
2      | art 2    | 8
3      | art 3    | 35

votos

id     | article_id    | vote
------------------------------
1      | 1             | 1
2      | 1             | 2
3      | 1             | 2
4      | 2             | 10
5      | 2             | -2
6      | 3             | 10
7      | 3             | 15
8      | 3             | 12
9      | 3             | -2

Cuando se inserta un nuevo registro en la tabla de votes , quiero actualizar el campo sum_votes en la tabla articles calculando la suma de todos los votos.

La pregunta

De qué forma es más eficiente, si el cálculo SUM () en sí es muy pesado (la tabla de votes tiene 700,000 registros).

1. Creando un disparador

CREATE TRIGGER `views_on_insert`
AFTER INSERT
ON `votes`
FOR EACH ROW
BEGIN
   UPDATE `articles` SET
       sum_votes = (
           SELECT SUM(`vote`)
           FROM `votes`
           WHERE `id` = NEW.article_id
       )
    WHERE `id` = NEW.article_id;
END;

2. Usando dos consultas en mi aplicación

SELECT SUM(`vote`) FROM `votes` WHERE `article_id` = 1;
UPDATE `articles` 
   SET sum_votes = <1st_query_result> 
 WHERE `id` = 1;

La primera forma parece más limpia, pero ¿se bloqueará la tabla todo el tiempo que se ejecute la consulta SELECT?

Answer 1

Acerca de los problemas de simultaneidad, tiene una forma ''fácil'' de evitar cualquier problema de simultaneidad en el segundo método, dentro de su transacción realice una selección en la línea de artículos (la For update está ahora implícita). Cualquier inserción simultánea en el mismo artículo no podrá obtener este mismo bloqueo y lo esperará.

Con los nuevos niveles de aislamiento predeterminados, sin usar el nivel de serialización en la transacción, no vería ninguna inserción simultánea en la mesa de votación hasta el final de su transacción. Entonces su SUMA debe ser coherente o parecer coherente . Pero si una transacción simultánea inserta un voto en el mismo artículo y se compromete antes que usted (y este segundo no ve su inserción), la última transacción que se comprometerá sobrescribirá el contador y perderá 1 voto. Así que realice un bloqueo de fila en el artículo utilizando una selección anterior (y haga su trabajo en una transacción, por supuesto). Es fácil de probar, abrir 2 sesiones interactivas en MySQL e iniciar transacciones con BEGIN.

Si usa el activador, se encuentra en una transacción de manera predeterminada. Pero creo que también debe realizar la selección en la tabla de artículos para hacer un bloqueo de fila implícita para los desencadenadores concurrentes en ejecución (más difícil de probar).

• No olvide eliminar desencadenantes.
• No te olvides de los activadores de actualizaciones.
• Si no usa activadores y permanece en el código, tenga cuidado de que cada consulta de inserción / eliminación / actualización en el voto deba realizar un bloqueo de fila en el artículo correspondiente antes de la transacción. No es muy difícil olvidar uno.

Último punto: realice transacciones más difíciles, antes de comenzar la transacción use:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

De esta manera, no necesita bloqueos de filas en los artículos, MySQL detectará que se produce una potencial escritura en la misma fila y bloqueará las demás transacciones hasta que finalice. Pero no use algo que haya calculado a partir de una solicitud previa . La consulta de actualización estará esperando un lanzamiento de bloqueo en los artículos, cuando se suelte el bloqueo en la primera transacción COMMIT el cómputo de SUM debe volver a realizar para contar. Por lo tanto, la consulta de actualización debe contener SUM o hacer una adición.

update articles set nb_votes=(SELECT count(*) from vote) where id=2; 

Y aquí verá que MySQL es inteligente, se detecta un interbloqueo si 2 transacciones intentan hacer esto mientras que la inserción se ha realizado en un momento concurrente. En niveles de serialización, no he encontrado la manera de obtener un valor incorrecto con:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN;
       insert into vote (...
       update articles set nb_votes=(
         SELECT count(*) from vote where article_id=xx
       ) where id=XX;
    COMMIT;

Pero prepárate para manejar la transacción de ruptura que debes rehacer.

Answer 2

prueba esto:

PHP: ¿Concepto de sistema de calificación de estrellas?

EDITAR: esquema cambiado para permitir que un usuario vote por la misma imagen muchas veces:

drop table if exists image;
create table image
(
image_id int unsigned not null auto_increment primary key,
caption varchar(255) not null,
num_votes int unsigned not null default 0,
total_score int unsigned not null default 0,
rating decimal(8,2) not null default 0
)
engine = innodb;

drop table if exists image_vote;
create table image_vote
(
vote_id int unsigned not null auto_increment primary key,
image_id int unsigned not null,
user_id int unsigned not null,
score tinyint unsigned not null default 0,
key (image_id, user_id)
)
engine=innodb;

delimiter #

create trigger image_vote_after_ins_trig after insert on image_vote
for each row
begin
 update image set 
    num_votes = num_votes + 1,
    total_score = total_score + new.score,
    rating = total_score / num_votes  
 where 
    image_id = new.image_id;
end#

delimiter ;

insert into image (caption) values (''image 1''),(''image 2''), (''image 3'');

insert into image_vote (image_id, user_id, score) values
(1,1,5),(1,2,4),(1,3,3),(1,4,2),(1,5,1),(1,5,2),(1,5,3),
(2,1,2),(2,2,1),(2,3,4),(2,3,2),
(3,1,4),(3,5,2);

select * from image;
select * from image_vote;