MySQL Forums
Forum List  »  InnoDB

InnoDB Locking Problem
Posted by: Geoff Mina
Date: June 16, 2005 12:08PM

Hello everyone... I am fairly new to MySQL, but not at all new to DBs and transactions in general. I have an issues which involves inserting a record into an InnoDB table with a primary key value which as already existed and been inserted from the table. The very high level is this:

1) There are two tables involved, a CURRENT table and a HISTORY table.

2) Records are inserted originally into the CURRENT table with an application generated primary key.

3) When a certain event occurs the record is moved from the CURRENT table to the HISTORY table using a INSERT INTO `HISTORY` SELECT * FROM `CURRENT` WHERE ID = [ID]

4) After the record is moved, the original record is deleted from the CURRENT table.

>>>> ALL IS GOOD UP UNTIL THIS POINT <<<<<

5) When a seperate event occurs in the application, a record can be moved from HISTORY back into CURRENT using aN INSERT INTO `CURRENT` SELECT * FROM `HISTORY` WHERE ID = [ID]

During the process of step 5 an error occurs. The transaction simply hangs, and eventually throws the following error message: "Lock wait timeout exceeded; try restarting transaction"

I know the original DELETE from the CURRENT table does NOT involve a pending transaction... I even went as far as restarting the MySQL server between the DELETE and the RE-INSERT. I have included the table definitions as well as the SHOW INNODB STATUS output while the LOCK is occuring.

Any help is greatly appreciated!
-Geoff


>>>>>>>>>>>>>>>> TABLE DEFINTIONS <<<<<<<<<<<<<<<<<<
CREATE TABLE `tbl_acd_agent_logins` (
-- CURRENT TABLE
`agent_login_id` bigint(20) NOT NULL default '0',
`agent_id` int(11) NOT NULL default '0',
`agent_phone` varchar(20) NOT NULL default '',
`agent_state` varchar(20) NOT NULL default '',
`state_dts` datetime NOT NULL default '0000-00-00 00:00:00',
`login_dts` datetime default NULL,
`logout_dts` datetime default NULL,
`calls_handled` int(11) NOT NULL default '0',
`reserved_UII` varchar(30) default NULL,
`reserved_dts` datetime default NULL,
`last_call_dts` datetime default NULL,
`next_call_time` datetime default NULL,
`login_hashcode` varchar(50) default NULL,
`is_ghost_login` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`agent_login_id`),
UNIQUE KEY `agent_id_unique` (`agent_id`),
KEY `agent_id` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



CREATE TABLE `tbl_acd_agent_logins_history` (
--HISTORY TABLE
`agent_login_id` bigint(20) NOT NULL default '0',
`agent_id` int(11) NOT NULL default '0',
`agent_phone` varchar(20) NOT NULL default '',
`agent_state` varchar(20) NOT NULL default '',
`state_dts` datetime NOT NULL default '0000-00-00 00:00:00',
`login_dts` datetime default NULL,
`logout_dts` datetime default NULL,
`calls_handled` int(11) NOT NULL default '0',
`reserved_UII` varchar(30) default NULL,
`reserved_dts` datetime default NULL,
`last_call_dts` datetime default NULL,
`next_call_time` datetime default NULL,
`login_hashcode` varchar(50) default NULL,
`is_ghost_login` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`agent_login_id`),
KEY `agent_id` (`agent_id`),
KEY `login_hashcode` (`login_hashcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


>>>>>>>>>>>>>>>> SHOW INNODB STATUS <<<<<<<<<<<<<<<<<<
Status

=====================================
050616 13:55:30 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 13, signal count 13
Mutex spin waits 4, rounds 60, OS waits 1
RW-shared spins 18, OS waits 9; RW-excl spins 2, OS waits 2
------------
TRANSACTIONS
------------
Trx id counter 0 3079828
Purge done for trx's n:o < 0 3079809 undo n:o < 0 0
History list length 10
Total number of lock structs in row lock hash table 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 3079826, not started, OS thread id 492
MySQL thread id 12, query id 5523 localhost 127.0.0.1 IntelliQueue
---TRANSACTION 0 3079183, not started, OS thread id 3736
MySQL thread id 13, query id 5532 localhost 127.0.0.1 root
SHOW innodb status
---TRANSACTION 0 3079825, not started, OS thread id 2148
MySQL thread id 11, query id 5525 localhost 127.0.0.1 IntelliQueue
---TRANSACTION 0 3079827, not started, OS thread id 3208
MySQL thread id 10, query id 5531 localhost 127.0.0.1 IntelliQueue
---TRANSACTION 0 3079823, ACTIVE 3 sec, OS thread id 3420 inserting
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 8, query id 5499 localhost 127.0.0.1 IntelliQueue Sending data
INSERT INTO tbl_acd_agent_logins SELECT * FROM tbl_acd_agent_logins_history WHERE agent_login_id = 33914880
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1542 n bits 80 index `agent_id_unique` of table `intelliqueue/tbl_acd_agent_logins` trx id 0 3079823 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

------------------
---TRANSACTION 0 3079821, ACTIVE 3 sec, OS thread id 272
2 lock struct(s), heap size 320
MySQL thread id 9, query id 5485 localhost 127.0.0.1 IntelliQueue
Trx read view will not see trx with id >= 0 3079822, sees < 0 3079822
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 1
133 OS file reads, 98 OS file writes, 44 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.67 writes/s, 0.67 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 3.33 non-hash searches/s
---
LOG
---
Log sequence number 0 65442889
Log flushed up to 0 65442889
Last checkpoint at 0 65442710
0 pending log writes, 0 pending chkp writes
29 log i/o's done, 0.33 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 14263952; in additional pool allocated 421888
Buffer pool size 512
Free buffers 388
Database pages 123
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 2, single page 0
Pages read 123, created 0, written 64
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1148, state: flushing buffer pool pages
Number of rows inserted 5, updated 3, deleted 8, read 23
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Options: ReplyQuote


Subject
Views
Written By
Posted
InnoDB Locking Problem
3336
June 16, 2005 12:08PM
2035
June 16, 2005 12:20PM
2226
June 16, 2005 01:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.