>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
