List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 3 1999 5:47pm
Subject:Re: Concurrent read & write (Was Re: read only tables?)
View as plain text  
At 11:06 AM -0500 12/3/99, Andy wrote:
>At 12:52 PM 12/2/99 -0600, you wrote:
>>>>   miso@stripped wrote:
>>>>
>>>>   > I have a rather small table with 4,000 rows and some 20
>>>>   > columns. The table is being updated heavily by one thread
>>>>   > all the time using UPDATE command. There's like 30 threads
>>>>   > that run SELECT queries on the table from web based
>>>>   > applications. Since there's so many SELECTs on the table the
>>>>   > UPDATEs are backing up. In other words those updates take
>>>>   > ages to execute.
>>>>
>[snip]
>
>>>Would it be possible to run 2 mysql daemons on the same
>>>physical files. One would be used for updating, the other
>>>for selecting only. So that the UPDATEs would run
>>>independently of the SELECTs and not compete for the same
>>>locks? Occasionaly I have to run some DELETE and INSERT
>>>statements - would this cause any trouble in the 2 daemon
>>>setup?
>>
>>Yes, it would.  The two servers must cooperate anyway,
>>or you'll get garbage back from your SELECTs while the
>>tables are being updated.  The difference is that the
>>servers must cooperate using external locking, and that
>>would be a lot slower than having a single server that
>>manages its own locks.
>>
>>Your desire not to compete for locks runs afoul of the
>>fact that locks are necessary to keep from selecting
>>from the table during the update.
>
>Could you clarify? Are you saying that every time mysql executes a SELECT,
>it implicitly acquires a read lock on the affected table, and every time it
>executes an INSERT or UPDATE, it implicitly acquires a write lock on the
>affected table?

Multiple selects can be happening on a table at one, but only one
write operation (INSERT, DELETE, UPDATE, REPLACE).  MySQL acquires the
locks automatically.

>If that's true, basically the database is reduced to single-threaded because
>only one UPDATE can happen at a time.

Single-threaded *for that table*, you mean.

>Is there any way to achieve concurrent INSERTs and SELECTs on the same table?

There is work on this in the current development series.  The addition of
that capability is probably no more than a week old.  I *think* this is
in 3.23.7, which isn't released yet.

>
>The application I'm working on needs to support a huge number of INSERTs and
>SELECTs (kind of like an auction system or a bulletine board). My server
>have multiple disks in RAID 0. I was hoping with RAID 0 I can have
>concurrent INSERTs and SELECTs on the same table. Can I?

Not yet.


>So is there any way to do SELECT without acquiring locks?

That is a question that doesn't even make sense.  Even if you don't acquire
a "lock" in the traditional sense, you must have some way to keep different
threads from interfering with each other, and that is in effect inducing
a locking protocol on table operations.

>Or better, is there any way to do row-level locks (ie. without locking the
>whole table)?

The GET_LOCK() function can be used for this, but it's for advisory locking
between cooperating processes only.  As soon as you try to write to a
table, a table-level exclusive lock is required.

>
>Anyone have any other suggestions to achieve concurrent read & write on the
>same table?

Wait until the simultaneous INSERT/SELECT work is stable, then use that.

-- 
Paul DuBois, paul@stripped
Thread
Concurrent read & write (Was Re: read only tables?)Andy3 Dec
  • Re: Concurrent read & write (Was Re: read only tables?)Scott Hess3 Dec
    • Re: Concurrent read & write (Was Re: read only tables?)miso3 Dec
      • Re: Concurrent read & write (Was Re: read only tables?)Scott Hess3 Dec
        • Re: Concurrent read & write (Was Re: read only tables?)miso4 Dec
          • Re: Concurrent read & write (Was Re: read only tables?)Scott Hess4 Dec
Re: Concurrent read & write (Was Re: read only tables?)Paul DuBois3 Dec
Re: Concurrent read & write (Was Re: read only tables?)Andy3 Dec
Re: Concurrent read & write (Was Re: read only tables?)Andy3 Dec
  • Re: Concurrent read & write (Was Re: read only tables?)Scott Hess4 Dec
    • Re: Concurrent read & write (Was Re: read only tables?)Patrick Greenwell4 Dec
Re: Concurrent read & write (Was Re: read only tables?)Andy4 Dec
  • Re: Concurrent read & write (Was Re: read only tables?)Sasha Pachev4 Dec
    • Re: Concurrent read & write (Was Re: read only tables?)miso7 Dec
      • Re: Concurrent read & write (Was Re: read only tables?)sinisa7 Dec
  • Re: Concurrent read & write (Was Re: read only tables?)Benjamin Pflugmann6 Dec
  • Re: Concurrent read & write (Was Re: read only tables?)Scott Hess6 Dec
Re: Concurrent read & write (Was Re: read only tables?)Andy7 Dec
  • Re: Concurrent read & write (Was Re: read only tables?)Benjamin Pflugmann7 Dec
Re: Concurrent read & write (Was Re: read only tables?)Andy7 Dec
  • Re: Concurrent read & write (Was Re: read only tables?)Scott Hess7 Dec