How do I lock rows in a union query so that I know they won't change during
the rest of my transaction?
I want to do the following query, using "LOCK IN SHARE MODE":
(select id from table1 where id > 1)
union
(select id from table2 where id > 1);
If I try:
(select id from table1 where id > 1 LOCK IN SHARE MODE)
union
(select id from table2 where id > 1);
it doesn't appear to do the locking. Another process can delete a record
from table1 which was contained
in the result. It appears that I see a consistent snapshot, and the record
is gone after I commit.
(See below.) I get the same result if I use FOR UPDATE.
MySQL doesn't allow me to put LOCK IN SHARE MODE after the second select or
after the whole union.
I'm using MySQL 4.0.13-nt-log.
---------- Example follows
In the following, table1 and table2 have the following structure:
CREATE TABLE `table1` (
`id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=InnoDB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> (select id from table1 where id > 1 LOCK IN SHARE MODE) union (select
id from table2 where id > 1);
+----+
| id |
+----+
| 3 |
| 2 |
+----+
2 rows in set (0.00 sec)
******************************* another process does: "delete from table1
where id=3;"
mysql> (select id from table1 where id > 1 lock in share mode) union (select
id from table2 where id > 1);
+----+
| id |
+----+
| 3 |
| 2 |
+----+
2 rows in set (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> (select id from table1 where id > 1 lock in share mode) union (select
id from table2 where id > 1);
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
