innodb use outside of explicit transactions

[Marc Slemko]

Suppose I have an innodb table in 4.0.14 and do:

LOCK TABLE maggie
INSERT INTO maggie values(123, 'simpson');
UNLOCK TABLES

As soon as I issue LOCK TABLE, any transaction in progress is
automatically committed.

By what point is this INSERT guaranteed to be committed to disk
(ie. redo log)?

Is it:

1. before INSERT returns?

2. before UNLOCK TABLES returns?

3. before it is read by any separate transaction?

4. before any separate transaction that read this data is committed?

5. sometime, no guarantee? This would seem to violate transactional
integrity of the new transaction if it had a foreign key reference.

My best guess is (1) since nothing else makes much sense, but I am
having trouble finding any documentation addressing exactly how
table locks interact with innodbs transaction model, aside from a
reference to being able to get a table lock while innodb row locks already
exist on the table.

I do have an explicit reason for wanting to do a lock tables instead of
doing everything in transactions, but I still require some assurances
that things are committed to disk so they can be recovered (cluster
with shared disk to fail over to a secondary node). I have an
innodb table like this:

CREATE TABLE maggie (
maggieid INT NOT NULL,
word VARCHAR(254),
modified TIMESTAMP NOT NULL,

PRIMARY KEY (maggieid)
)

There are multiple writers that can each add or modify a row in the
table.

There are multiple readers, each one keeping a full representation
of the table in memory and polling at intervals for modified columns
based on the timestamp.

They loop:

1. grab new timestamp
2. select from maggie where modified >= old timestamp
3. save new timestamp for the next round

The problem is that if I just do a normal update in the writer,
then there is a race between when the timestamp is updated and the
transaction is committed. If a reader comes in, it won't see
the update since it isn't committed, it won't block on it because of
multiversioning , and will never see it next time around since the
timestamp is too old. I'm considering if I can remove the race by
using LOCK TABLES explicitly in the writer... hence my first
question. I couldn't see any obvious way to do what I want using
only innodb row level locks, no matter what isolation level I used.

Suggestions or pointers at docs?

thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw