How are innodb tables locked when the ON INSERT trigger is started?

Question

I have two innodb tables:

articles

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

of votes

 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 

When a new record is inserted into the votes table, I want to update the sum_votes field in the articles table, calculating the sum of all votes.

Question

Which method is more efficient if calculating SUM () itself is very difficult (the votes table has 700K entries).

1. Create a trigger

 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. Using two queries in my application

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

The first method seems cleaner, but will the table be locked all the time the SELECT query is executed?

Answer 1

About concurrency problems, you have a "simple" way to prevent any concurrency problems in the second method, inside your transaction, a selection is made in the article line (now For update implicit). Any matching insert in the same article will not be able to get the same lock and will be waiting for you.

With the new default isolation levels, even without using the serialization level in the transaction, you will not see any parallel insertion in the voting table until the end of the transaction. Thus, your SUM should remain consistent or look coherent . But if a parallel transaction inserts a vote on the same article and commits in front of you (and this second one does not see your insert), the last transaction to commit will overwrite the counter, and you will lose 1 vote. So lock the rows on the article using the selection before (and, of course, do your job in a transaction). It is easy to test, open 2 interactive sessions in MySQL and start transactions with BEGIN.

If you use a trigger, you are in a transaction by default. But I think you should also make a selection in the article table to make implicit row locking to trigger simultaneous triggers (harder to check).

• Do not forget the delete triggers.
• Do not forget about update triggers.
• If you do not use triggers and remain in the code, be careful to insert / delete / update the request for voting; you must lock the line on the corresponding article in the transaction. It is not very difficult to forget.

Last point: perform more complex transactions before using the transaction:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

Thus, you do not need row locks in articles, MySQL detects that a potential record in the same row occurs and blocks the transactions of others until you finish. But do not use what you calculated from the previous query. The update request will wait for the release of the lock for articles, when the lock is released by the first COMMIT transaction, the SUM calculation must be done again to count. Thus, the update request must contain SUM or make an addition.

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

And here you will see that MySQL is smart, a deadlock is detected if 2 transactions try to do this, while the insert was executed at the same time. In serialization levels, I did not find a way to get the wrong value with:

  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; 

But be prepared to process a transaction that you must complete again.

Answer 2

try the following:

PHP: star rating system concept?

EDIT: The scheme has changed to allow the user to vote for the same image many times:

 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;