Hi! ----- Original Message ----- From: "Galen Wright-Watson" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, June 20, 2002 11:32 PM Subject: Re: locking on row level ...
> > On Wed, 19 Jun 2002, Silmara Cristina Basso wrote: > > > I'm newbie MySQL and I'm using MySQL-max 4.0.1(Innodb), with one application > > developed in Delphi and connect through MyODBC. The question is ... > > How can i do to lock one row so that it is editing in the application? > > > > > > The impression I get from the documentation is "MySQL only supports table > locking for ISAM/MyISAM and HEAP tables and page level locking for BDB tables." 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. Note that if you do UPDATE mytable SET mycol2 = yyy WHERE mycol = xxx; then InnoDB automatically locks the row with an exclusive lock. Note also that an ordinary SELECT SELECT * FROM mytable WHERE mycol = xxx; does NOT lock the row. It is a 'consistent read' which reads the database as it is at a point in time, seeing only the committed transactions (and also the current transaction of the user who does the SELECT). Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB --------------------------------------------------------------------- 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
