Bill, I tested this on the latest 4.1.1 snapshot, and it worked ok: client 2 waited for client 1 to commit. Please test again.
Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html Client 1: mysql> CREATE TABLE `table1` ( -> `id` int(11) NOT NULL default '0', -> PRIMARY KEY (`id`) -> ) TYPE=InnoDB -> -> ; Query OK, 0 rows affected (0.10 sec) mysql> CREATE TABLE `table2` ( -> `id` int(11) NOT NULL default '0', -> PRIMARY KEY (`id`) -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> set autocommit = 1; Query OK, 0 rows affected (0.00 sec) mysql> insert into table2 values (2) -> ; Query OK, 1 row affected (4.78 sec) mysql> insert into table1 values (3); Query OK, 1 row affected (5.66 sec) 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 (33.36 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> Client 2: mysql> delete from table1 -> where id=3; Query OK, 1 row affected (22.33 sec) mysql> .................... From: "Bill Easton" ([EMAIL PROTECTED]) Subject: InnoDB "lock in share mode" on union This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2003-11-10 07:05:16 PST 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]
