We have a table in the database holding session data. The data is updated rather oftenly using REPLACE INTO query with primary key. Table structure is: +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | sesskey | varchar(32) | | PRI | | | | stamp | timestamp(14) | YES | MUL | NULL | | | value | text | | | | | | secure_key | varchar(32) | YES | MUL | NULL | | +------------+---------------+------+-----+---------+-------+
Now, once a day there's a query launched that should delete all records with timestamp older than X days. The problem is that it seems to be very slow and lock out a lot of updates, which are essential for site workings, since the session page lifecycle goes as: SELECT session data ....do some stuff... REPLACE INTO session data. The error that is produced is: 1213 (Deadlock found when trying to get lock; Try restarting transaction) First I have used the regular ISAM tables, but it just made the REPLACES get stuck and the load on the servers rise. Then I replaced it with InnoDB table, which should do better since the 'dead' rows to be removed should not, theoretically, prevent data in 'live' rows from updating. However, looks like it is not so - DELETE is still blocking REPLACE's and I'm getting that error about deadlock. Am I doing something wrong? Is there any other way to make DELETE not block the updates? Maybe some advices about reworking the scheme so that cleanup could be doen without blocking the work? The table has about 300.000 rows. TIA, -- Stanislav Malyshev, Zend Products Engineer [EMAIL PROTECTED] http://www.zend.com/ +972-3-6139665 ext.109 --------------------------------------------------------------------- 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
