Re: InnoDB Record Lock Command
something along the lines of: mysql> select * from where = lock in share mode On Sep 5, 2005, at 4:01 PM, Rich wrote: Hi there. I am looking for the syntax to lock individual records in an InnoDB table. I can't seem to find anything outside of locking full tables. Rich Allen A kidney transplant saved my life, plese consider being an organ donor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Record Lock Command
Hi there. I am looking for the syntax to lock individual records in an InnoDB table. I can't seem to find anything outside of locking full tables. Any urls out there? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Record Lock?
Michael, If the database is twice the size, how will that affect performance? I don't use full-text searches, so that shouldn't be a problem. Thanks, Dan -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 10:28 AM To: [EMAIL PROTECTED] Subject: Re: Record Lock? On Sun, Dec 08, 2002 at 12:06:53PM -0800, Richard Reina wrote: >> How do I switch from MYISAM to InnoDB? Are there any negative >> implication in swithing? > > ALTER table TYPE=InnoDB; It will take twice as much disk space or thereabouts and not support full text queries. SQL -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 - 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
Re: Record Lock?
On Sun, Dec 08, 2002 at 12:06:53PM -0800, Richard Reina wrote: How do I switch from MYISAM to InnoDB? Are there any negative implication in swithing? ALTER table TYPE=InnoDB; It will take twice as much disk space or thereabouts and not support full text queries. SQL -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
Re: Record Lock?
Benjamin, - Original Message - From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, December 09, 2002 9:58 AM Subject: Re: Record Lock? > Hi. > > On Sun 2002-12-08 at 14:50:35 +0200, [EMAIL PROTECTED] wrote: > [...] > > > SELECT * > > > FROM your_table > > > WHERE > > > FOR UPDATE > [...] > > I would like to clarify that InnoDB holds locks till the current transaction > > COMMIT or ROLLBACK. Thus, in the AUTOCOMMIT=1 mode is makes no sense to use > > FOR UPDATE. > > Thanks. I was not aware, that FOR UPDATE works this way. > > Then I have another question: Why would one use FOR UPDATE with > BEGIN/COMMIT? I thought, in the default transaction seperatation level > for InnoDB, REPEATED READ, I am guaranteed that nobody changed the > rows anyway. Correct? not correct. A plain SELECT in InnoDB is a 'consistent read'. It does not set any locks but reads a snapshot of the database. That is how under the default REPEATABLE READ isolation level you always get the same result set back, though other users have updated the table. You must use FOR UPDATE or LOCK IN SHARE MODE to lock the index records the SELECT touches. The exception is the isolation level SERIALIZABLE, where InnoDB internally adds LOCK IN SHARE MODE to each plain SELECT. ... > > Benjamin. > > -- > [EMAIL PROTECTED] sql query Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - 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
Re: Record Lock?
Hi. On Sun 2002-12-08 at 14:50:35 +0200, [EMAIL PROTECTED] wrote: [...] > > SELECT * > > FROM your_table > > WHERE > > FOR UPDATE [...] > I would like to clarify that InnoDB holds locks till the current transaction > COMMIT or ROLLBACK. Thus, in the AUTOCOMMIT=1 mode is makes no sense to use > FOR UPDATE. Thanks. I was not aware, that FOR UPDATE works this way. Then I have another question: Why would one use FOR UPDATE with BEGIN/COMMIT? I thought, in the default transaction seperatation level for InnoDB, REPEATED READ, I am guaranteed that nobody changed the rows anyway. Correct? So the only advantage would be, that in a long transaction I have already locked them early and cannot be rolled back later due to a deadlock. (Well, and this behaviour has changed in recent 4.0, IIRC). Does this mean, FOR UPDATE is mostly useful with lesser transaction seperation levels? TIA, Benjamin. -- [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
Re: Record Lock?
Benjamin Pflugmann wrote: Hello. On Sat 2002-12-07 at 14:15:59 -0800, [EMAIL PROTECTED] wrote: I would like to lock a specific record in a MySQL table so that no other user can update it while another user is is update the record via the user interface. Can someone please tell me the easiest way to do this? Depends on the table handler. With MYISAM, you can only lock the whole table (via LOCK TABLE). So you would rather refrain from that way of assuring no change has made. With InnoDB you can lock a row by selecting it: Ben, thanks so much for your respoonse. How do I switch from MYISAM to InnoDB? Are there any negative implication in swithing? SELECT * FROM your_table WHERE FOR UPDATE will lock that row for you. Of course, only as long as you keept the connection open. But usually it is a bad idea to implement application level locks by using database locks. I agree but it's only for in the office use on a small LAN so we could just walk over to the offending person computer and get them out -- atleast until I get some sort of timeout mechanism in place. What if I leave my computer? You have to implement some kind of timeout. And so on. Better to implement conflict handling correctly from the start. Any ideas here are of course welcomed. :-) HTH, Benjamin. - 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
Re: Record Lock?
Hi! - Original Message - From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, December 08, 2002 3:14 AM Subject: Re: Record Lock? > Hello. ... > With InnoDB you can lock a row by selecting it: > > SELECT * > FROM your_table > WHERE > FOR UPDATE > > will lock that row for you. Of course, only as long as you keept the > connection open. I would like to clarify that InnoDB holds locks till the current transaction COMMIT or ROLLBACK. Thus, in the AUTOCOMMIT=1 mode is makes no sense to use FOR UPDATE. When MySQL ends a connection, it rolls back the current transaction. Thus, locks are never kept after the connection ended. ... > HTH, > > Benjamin. 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 sql query - 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
Re: Record Lock?
Hello. On Sat 2002-12-07 at 14:15:59 -0800, [EMAIL PROTECTED] wrote: > I would like to lock a specific record in a MySQL table so that no other > user can update it while another user is is update the record via the > user interface. Can someone please tell me the easiest way to do this? Depends on the table handler. With MYISAM, you can only lock the whole table (via LOCK TABLE). So you would rather refrain from that way of assuring no change has made. With InnoDB you can lock a row by selecting it: SELECT * FROM your_table WHERE FOR UPDATE will lock that row for you. Of course, only as long as you keept the connection open. But usually it is a bad idea to implement application level locks by using database locks. What if I leave my computer? You have to implement some kind of timeout. And so on. Better to implement conflict handling correctly from the start. HTH, Benjamin. -- [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
Record Lock?
I would like to lock a specific record in a MySQL table so that no other user can update it while another user is is update the record via the user interface. Can someone please tell me the easiest way to do this? Thank you, Richard Reina - 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
