On Fri, Jun 21, 2002 at 12:38:06AM +0300, Heikki Tuuri wrote:
> InnoDB type tables in MySQL have row level locking. Call SET AUTOCOMMIT=0 or
> wrap your transaction in BEGIN ... COMMIT.
>
> Make sure you have an index on column 'mycol' so that InnoDB finds the row
> without a table scan. Then you can lock a row:
>
> 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
along--in theory. Most of the time this transaction fails on us with an error message
about a deadlock being detected. My current workaround is to now let this accumulate
for a week
and stop the web servers while I clean the tables out--very undesired.
The site is multilingual and is busy almost 24 hours a day, so there's no real slow
period where the maintenance scripts run with less contention issues.
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]
---------------------------------------------------------------------
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
