Force first update, then select in procedure

Question

I have this procedure in MySQL (do not worry, to understand what it does, just look at the part where the cursor opens)

/* PROCEDURE 1 : Post notification */ DROP PROCEDURE IF EXISTS AddNotificationOnPosts; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `AddNotificationOnPosts`(arg_from_user INT(11),arg_on_post_id INT(11),arg_in_group_id INT(11)) BEGIN DECLARE num_rows INT DEFAULT NULL; DECLARE insert_result INT DEFAULT NULL; DECLARE done INT DEFAULT 0; DECLARE var_user_id INT DEFAULT NULL; DECLARE c1 CURSOR FOR SELECT user_id FROM user_rights WHERE user_rights.right = 101 AND user_rights.group_id = arg_in_group_id ORDER BY user_id DESC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; IF(arg_from_user IS NULL OR arg_from_user = '') THEN SELECT "0" AS response; ELSEIF(arg_on_post_id IS NULL OR arg_on_post_id = '') THEN SELECT "0" AS response; ELSEIF(arg_in_group_id IS NULL OR arg_in_group_id = '') THEN SELECT "0" AS response; ELSE SELECT count(notification_id) FROM notifications_posts WHERE from_user = arg_from_user AND on_post_id = arg_on_post_id AND in_group_id = arg_in_group_id INTO num_rows; END IF; IF num_rows = 0 THEN INSERT INTO notifications_posts(from_user,on_post_id,in_group_id) VALUES(arg_from_user,arg_on_post_id,arg_in_group_id); SELECT ROW_COUNT() INTO insert_result; IF insert_result > 0 THEN /* Increment the notifications for every user*/ OPEN c1; read_loop: LOOP FETCH c1 INTO var_user_id; IF done THEN LEAVE read_loop; ELSE IF NOT(var_user_id = arg_from_user) THEN /* UPDATING */ UPDATE user_info SET notifications = notifications + 1 WHERE user_info.user_id = var_user_id; /* SELECTING RESULT */ SELECT user_info.user_id, messages, tasks, notifications, messages+tasks+notifications AS total FROM user_rights INNER JOIN user_info ON user_info.user_id = user_rights.user_id WHERE user_rights.right = 101 AND user_rights.group_id = arg_in_group_id ; END IF; END IF; END LOOP; CLOSE c1; ELSE SELECT "0" AS response; END IF; ELSE SELECT "0" AS response; END IF; END $$ DELIMITER ; 

and in this part

  /* Increment the notifications for every user*/ OPEN c1; read_loop: LOOP FETCH c1 INTO var_user_id; IF done THEN LEAVE read_loop; ELSE IF NOT(var_user_id = arg_from_user) THEN /* UPDATING */ UPDATE user_info SET notifications = notifications + 1 WHERE user_info.user_id = var_user_id; /* SELECTING RESULT */ SELECT user_info.user_id, messages, tasks, notifications, messages+tasks+notifications AS total FROM user_rights INNER JOIN user_info ON user_info.user_id = user_rights.user_id WHERE user_rights.right = 101 AND user_rights.group_id = arg_in_group_id ; END IF; END IF; END LOOP; CLOSE c1; 

the procedure selects the data, then updates it (believe me, I'm dead seriously), and I really need to select after, because I generate XML with the changed data, therefore:

1 Why do you then select the update, because I see that select is under the update

2 Who had this “brilliant idea” to do it this way?

Thanks.

Answer 1

When you have one batch update / deletion and something else in the sql server, it is completely indeterminate in what order the "commands" will be executed. The solution to this problem is to put the "update" in another batch using the "exec" operator. So try using:

 exec('UPDATE user_info SET notifications = notifications + 1 WHERE user_info.user_id =' + var_user_id); 

Now it should work.

Answer 2

I solved the problem: the SELECT must be outside CURSOR LOOP .

Answer 3

I would like the update and selection to be performed in the correct order, but the selection could not “see” the update result due to transaction isolation.

I would suggest the following solution to solve this problem and improve performance, eliminating the pool in the following selection:

 set @notification = 0; /* UPDATING */ UPDATE user_info SET notifications = @notification := notifications + 1 WHERE user_info.user_id = var_user_id; /* SELECTING RESULT */ SELECT user_info.user_id, messages, tasks, notifications, messages+tasks+@notifications AS total FROM user_rights WHERE user_rights.right = 101 AND user_rights.group_id = arg_in_group_id ;