Michael, ----- Original Message ----- From: "Michael Bacarella" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> > > SELECT * FROM mytable WHERE mycol = xxx LOCK IN SHARE MODE; > > > > or > > > > SELECT * FROM mytable WHERE mycol = xxx FOR UPDATE; > > > > > > The upper query sets a shared lock (= read lock), and the lower one an > > exclusive lock (= write lock) on the row. > > While we're on the subject (Heikki, I know I need to get back to you on something > else, will do that soon <G>), I'm having a problem that involves locks. > > Deadlocks. We maintain tables to store user session information. These > tables are updated constantly, and can grow quite large over time. A maintenance > script runs every day which deletes records older than 24 hours. > > In the olden days with MyISAM, ''DELETE FROM blah WHERE TS < timestamp'' would just set > a table lock and stop the site for a few seconds. With InnoDB, it of course keeps on chugging
you can set a table lock also on an InnoDB table with LOCK TABLES yourtable WRITE; before you do the big delete. This will not prevent all deadlocks or lock wait timeouts because some smaller transactions may have already acquired row locks on the table when the big transaction gets the table lock. In that case you can try wrapping also some of your smaller transactions inside LOCK TABLES to serialize them. > I can think of plenty of better workarounds, but I wanted to solicit input before > implementing something that I may not have to. > > Suggestions? ;) > > -- > Michael Bacarella | Netgraft Corporation > | 545 Eighth Ave #401 > Systems Analysis | New York, NY 10018 > Technical Support | 212 946-1038 | 917 670-6982 > Managed Services | [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- 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
