List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:August 14 1999 7:26pm
Subject:Re: lock table speed up DB access?
View as plain text  
At 14:17, 19990814, Ee Loon wrote:
>mysql documentation, chapter 10.13 states that
>locking of table speed up insertion. is this true if I
>only make use of one table? (because in 7.23, the manual
>says that "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". it also says that
>"Normally, you don't have to lock tables, as all single UPDATE statements
>are atomic").

MySQL locks the table for you when you do an operation on it.  For
example, when you perform an UPDATE on a table, other threads must
wait to SELECT on that table until your UPDATE is done.

The point of locking a table before doing a series of operations on
it is that, once you've locked the table yourself, you won't have to
wait for other who might start using the table between your operations.

This is just as applicable when you're working with a single table as
when you're working with several tables.

>my question was:
>  if i am trying to into into a single table 200 records, do i need to lock
>table to speed up insertion
>or i simply can do:
>"INSERT INTO a VALUES (record1),(record2),...,(record200)."

In this case, the INSERT is a single operation, even though you are
inserting many rows.  This is the fastest way to insert values into
a table.  You won't get any benefit from locking the table before
performing this operation, because other threads will not be able to
use the table until the entire insert is completed.

questionsAnonymous14 Aug
  • Re: questionsMartin Ramsch14 Aug
  • RE: lock table speed up DB access?Ee Loon14 Aug
    • Re: lock table speed up DB access?Thimble Smith14 Aug
      • Re: lock table speed up DB access?Martin Ramsch15 Aug
        • Re: lock table speed up DB access?Michael Widenius21 Aug
    • Re: lock table speed up DB access?Sasha Pachev15 Aug