I'm using mysql 3.23.51 w/ InnoDB tables, and am running into some locking issues which I don't believe should occur. There are 2 transactions that seem to interfere with each other. The first transaction is an update of one table followed by a select of another table, ie: BEGIN UPDATE table1 set counter = counter + 1 where value = 'table2'; SELECT * from table2 where state = 2;
there is an index on state in table 2. the second transaction is huge, with a ton of updates and inserts, this transaction may take several minutes: BEGIN LOTS OF: INSERT INTO table3 values(etc, etc.); UPDATE table2 set state = 2 where id = x and state = 1; COMMIT we are using the highest level of transactional security -- the term for it eludes me at the moment. so, what happens is that transaction 2 starts first, then transaction 1 starts and just freezes until the wait lock timeout occurs. however, it is my understanding that a select will not acquire any locks (from mysql docs: * SELECT ... FROM ... : this is a consistent read, reading a snapshot of the database and setting no locks.). And that all the updates made by the second transactions will not be accessible to the first transaction until the second transaction commits. So, my interpretation of what is actually occurring is that the index is getting changed by the second transaction before it commits, then the first transaction is trying to read a record changed by the 2nd transaction that the 2nd transaction still has a lock on, so it just sits around and waits until it timeouts because the 2nd transaction takes so long to execute. It doesn't seem to me that innodb is behaving correctly is this instance, but if I'm wrong, please let me know. Does anyone have any other ideas on what the problem could be? thanks for you help, joe --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
