SELECT and UPDATE same rows in MYSQL stored procedure

Question

I'm working on a application for which I need to read m rows at a time out of total 'N' rows where m < N. Every time I read 'm' rows I have to set their status as read in the same table.

For example consider following table

+------+-------------------------+--------------------------+----------+-------+---------+------+
| ID   | from_email_address      | to_email_address         | subject  | body  | inqueue | sent |
+------+-------------------------+--------------------------+----------+-------+---------+------+
|    1 | 0120sushil@gmail.com    | kumar.sushil@outlook.com | Subject1 | Body1 |            0 |    0 |
|    2 | 0120ksushil@gmail.com   | kumar.sushil@outlook.com | Subject1 | Body1 |          0 |    0 |
|    3 | shivaseth1@gmail.com    | kumar.sushil@outlook.com | Subject1 | Body1 |       0     |    0 |
|    4 | shivaseth1@gmail.com    | amanrajg@outlook.com     | Subject1 | Body1 |       0     |    0 |
|    5 | shivamprakash@gmail.com | amanrajg@outlook.com     | Subject1 | Body1 |           0 |    0 |
|    6 | shivamprakash@gmail.com | poorvanagpal@outlook.com | Subject1 | Body1 |       0 |    0 |
|    7 | shivankgupta@gmail.com  | poorvanagpal@outlook.com | Subject1 | Body1 |       0 |    0 |
+------+-------------------------+--------------------------+----------+-------+---------+------+

I want to read lets say 3 rows at a time and once I have read the rows I want to set inqueue status of those rows as 1.

I can use following query in stored procedure to select the rows

select * from EmailQueue where inqueue=1 LIMIT 3

After this how to update the same rows and set their inqueue to 1.

EDIT

Here is the stored procedure I created which is giving some error.

DELIMITER $$
DROP PROCEDURE IF EXISTS GetUnsentMails;
CREATE PROCEDURE GetUnsentMails()
BEGIN
START TRANSACTION;
    CREATE TEMPORARY TABLE temp_EmailQueue AS SELECT * FROM EmailQueue WHERE inqueue = 0 LIMIT 5 FOR UPDATE;
    UPDATE EmailQueue SET inqueue=1 where id in (SELECT id from temp_EmailQueue) AND inqueue = 0;
COMMIT;
END

It gives following error on calling

ERROR 1746 (HY000): Can't update table 'emailqueue' while 'temp_EmailQueue' is being created.

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

Suggesting to use Transaction and "SELECT FOR UPDATE" to fulfill your requirements.

Please refer following links for the examples:

MySQL 'select for update' behaviour

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

http://www.sqlines.com/mysql/how-to/select-update-single-statement-race-condition

UPDATE - Added the QUERY EXAMPLE:

Example:

.......
#Before starting procedure
.......
START TRANSACTION;
CREATE TEMPORARY TABLE zzz_EmailQueue AS SELECT * FROM EmailQueue WHERE inqueue=1 LIMIT 3 FOR UPDATE;
.....
.....
#Section for other activities....
.....
.....
UPDATE EmailQueue SET inqueue=<<New_Value>> WHERE id IN (SELECT id FROM zzz_EmailQueue) AND inqueue=1;
COMMIT;
.......
#Remaining lines of Prodecure
.......

Update ** **Try with following method:

DECLARE v_EmailQueue_ID DOUBLE;
SELECT ID INTO v_EmailQueue_ID FROM EmailQueue WHERE inqueue = 0 LIMIT 1 FOR UPDATE;
UPDATE EmailQueue SET inqueue=1 WHERE id=v_EmailQueue_ID AND inqueue = 0;

edited May 23 '17 at 11:58

Community

1
1

answered Jun 15 '14 at 09:34

Suresh Gautam

816
8
21

That's fine. But what query to use for updating those rows only which were selected by `SELECT` query ? – kaysush Jun 15 '14 at 09:44
I tried your solution but when I call the procedure it says `ERROR 1746 (HY000): Can't update table 'emailqueue' while 'zzz_EmailQueue' is being created.` Please check the update. – kaysush Jun 15 '14 at 11:42
Which version of Mysql you are suing? I have Verified above methods in Mysql 5.5.27. – Suresh Gautam Jun 15 '14 at 11:55
I'm using MySql 5.6.19 – kaysush Jun 15 '14 at 12:11
Horrible!!! Anyway, variable can be used instead of temporary to hold EmailQueueID which you are going to update if it's feasible to proceed one email queue at a time (i.e. LIMIT 1). – Suresh Gautam Jun 15 '14 at 12:32
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/55651/discussion-between-kaysush-and-suresh-gautam). – kaysush Jun 15 '14 at 12:50

SELECT and UPDATE same rows in MYSQL stored procedure

1 Answers1