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/[EMAIL PROTECTED]