More locking issues
Hi, I'm new to MySQL. I see you all talking about locking databases etc... but isn't that the point of having a database server? So you don't have to worry about record locks? I'm designing the backend for a popular site where a user's visit will result in multiple read/write operations on a database. Is record locking something I definitely need to do? Thanks, Jeff - 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: More locking issues
Hi, If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 6:57 PM Subject: More locking issues Hi, I'm new to MySQL. I see you all talking about locking databases etc... but isn't that the point of having a database server? So you don't have to worry about record locks? I'm designing the backend for a popular site where a user's visit will result in multiple read/write operations on a database. Is record locking something I definitely need to do? Thanks, Jeff - 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: More locking issues
Gelu Gogancea wrote: If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Can I ask if this is in fact accurate? As I understand it, MySQL does a fairly good job of locking the tables, that's part of the performance problem of MyISAM vs. InnoDB table types with many updates running. And with InnoDB, you get a Repeatable Read isolation level by default which 'feels like' getting a read lock. What did you mean that it doesn't lock tables? 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: More locking issues
Hi, Is not the same caseJeff didn't said anything about TRANSACTIONS. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael T. Babcock [EMAIL PROTECTED] To: Gelu Gogancea [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Jeff Snoxell [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 8:44 PM Subject: Re: More locking issues Gelu Gogancea wrote: If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Can I ask if this is in fact accurate? As I understand it, MySQL does a fairly good job of locking the tables, that's part of the performance problem of MyISAM vs. InnoDB table types with many updates running. And with InnoDB, you get a Repeatable Read isolation level by default which 'feels like' getting a read lock. What did you mean that it doesn't lock tables? 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: More locking issues
From MySQL Reference Manual: ... Normally, you don't have to lock tables, as all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. There are a few cases when you would like to lock tables anyway: * If you are going to run many operations on a bunch of tables, it's much faster to lock the tables you are going to use. The downside is, of course, that no other thread can update a READ-locked table and no other thread can read a WRITE-locked table. * If you are using a table handler in MySQL that doesn't support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown below requires LOCK TABLES in order to execute safely: mysql LOCK TABLES trans READ, customer WRITE; mysql select sum(value) from trans where customer_id= some_id; mysql update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql UNLOCK TABLES; Without LOCK TABLES, there is a chance that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements. By using incremental updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, you can avoid using LOCK TABLES in many cases. ... That is the way I work with MySQL. In almost all cases I do not need to lock tables. I only use it when I am updating more than one tables, because of chances of getting data integrity broken. Hope I could help. []s Anderson Pereira Ataides Em Qui 12 Dez 2002 16:09, you wrote: Hi, If you have more than one user connected to the database which handled the same tables, you need to use LOCK/UNLOCK tables. MySQL daemon don't lock tables or records if you don't require this. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 6:57 PM Subject: More locking issues Hi, I'm new to MySQL. I see you all talking about locking databases etc... but isn't that the point of having a database server? So you don't have to worry about record locks? I'm designing the backend for a popular site where a user's visit will result in multiple read/write operations on a database. Is record locking something I definitely need to do? Thanks, Jeff - 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 --- - 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
