lock InnoDB table for reading having AUTOCOMMIT=1

[kittyyo]

I'm using MySQL 4.1.7 and InnoDB tables. I'd like to lock some tables for (write and) read of other users during a transaction, like this:

START TRANSACTION;
LOCK TABLES T1, T2;
...
...
UNLOCK TABLES T1, T2;
COMMIT;

Since I have AUTOCOMMIT=1, this does not work: "You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES" (according to

http://dev.mysql.com/doc/mysql/en/lock-tables.html).

Do I have an alternative, having AUTOCOMMIT=1, to lock tables for read access during a transaction? I considered SELECT * FROM table FOR UPDATE, but this does not seem to perform a read lock.

Thank you for any suggestions,
Anne

 

[Bastien]

transaction automatically lock the rows that you need with InnoDB. Why lock the table?

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[kittyyo]

I'd like to lock the whole table for reading.
From the moment I'm locking the table the data should be not accessible at all for all other users.

Anne

 

[Bastien]

But why? That brings your app to a screaming halt. And if the locking is not resolved correctly. you'll deadlock the db

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[kittyyo]

OK, I did it now using a flag in the database that other useres must poll before acessing the table, therefore tnot really read-locking the table.

Anyway, it would have been interesting if a real read-lock is possible.

Thanks,
Anne

 

[ericbrunson]

Again, with an atomic commit, there is no reason to lock your table. The updated data is not visible to other users until the update transaction completes, you are wasting your time and resources by not listening to Bastien.

 

[kittyyo]

I am sorry. I think I got the question wrong. Sorry to have bothered you.

I didn't want to block users from reading the new (uncommitted) data, but from reading the old (already committed before the locking transaction would start) data.

Anyway, thanks for responding, and excuse me if I got something wrong again.

Anne

 

[Bastien]

Reading the old committed data is the nature of the db. Its called 'read consistency'. Its the way dbs are supposed to work to provide people with the complete data either before or after a change, not during a change in the data before a commit.

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...