Hi!
<disclaimer>
this is a less technical answer with far less details than Ingo's. Read
his reply for details, below is just an overview</disclaimer>
On Jul 16, Jay Pipes wrote:
>
> I've been doing a bit of digging around for an article I'm writing on
> the MyISAM concurrent insert functionality and have run into a
> frustrating lack of understanding on *when* exactly file locks are
> placed on the .MYD data file during connection thread execution.
file locks aren't placed on .MYD file at all. They may be placed on .MYI
file if --external-locking is enabled, but they only protect against
concurrent access by other processes that also use --external-locking.
File locks don't prevent other threads from accessing the file.
> For the purposes of this inquiry, assume a fixed MyISAM record format,
> so we'd be talking about the _mi_write_static_record() function (as tied
> to the MYISAM_SHARE.write_record function pointer). If you take a look
> at this stripped-down version of that function:
This is, basically, irrelevant. Locks are placed before data are
accessed, if you're in _mi_write_static_record, it means all locks are
already in place.
> Your insight would be GREATLY appreciated, as my eyesight is beginning
> to blur as I grep my way through countless function pointer redirections
> and redefines in /storage/myisam and /mysys
heh :)
ok.
There's lock manager in MySQL (thr_lock.c), it locks on a table-level.
In the beginning of the file there's a list of all implemented lock
levels, and a comment about concurrent insert support (what are the
callbacks from the lock handler into the storage engine, and how they're
used). For your question, it's not important to understand how lock
manager works, it's enough to know that it does. Consider it a
(overcomplicated) black box.
When you SELECT from a MyISAM table, MySQL tries to place a TL_READ lock
on the table. When you do UPDATE - it's TL_WRITE lock (let's forget
about TL_READ_HIGH_PRIORITY and TL_WRITE_LOW_PRIORITY locks for now :).
TL_READ and TL_WRITE are incompatible, which means only one can be held
at time. Lock manager guarantees it. That's why SELECT and UPDATE can
never be executed concurrently on a MyISAM table. Note that it's
completely handled in MySQL, MyISAM needs no file locking, no support
for locking whatsoever, no nothing. MyISAM can also happily assume that
no two threads will be modifying the same table concurrently, and it
needs nothing to do to protect against it - it also happens
automatically (TL_WRITE is incompatible with TL_WRITE). Two SELECT's can
read the table in parallel, because TL_READ is compatible with TL_READ -
which means many TL_READ can be held at the same time.
The above was not specific to MyISAM, it's just how lock manager works
in MySQL, any storage engine that doesn't have its own lock manager will
behave this way - CSV, ARCHIVE, MERGE, HEAP, to name a few.
Now, concurrent inserts. When you INSERT into MyISAM table, MySQL uses
TL_WRITE_CONCURRENT_INSERT lock. Tries to use, that is - lock manager
asks the storage engine, using a special callback function, whether
concurrent insert can be used. MyISAM table, for example, needs to have
no holes for concurrent insert to be allowed. If the answer is negative,
TL_WRITE_CONCURRENT_INSERT is changed to TL_WRITE.
Note, TL_WRITE_CONCURRENT_INSERT is compatible with TL_READ, which means
one thread can insert while others are reading from the table. But
TL_WRITE_CONCURRENT_INSERT is not compatible with itself - only one
thread can insert data.
That's basically the answer to your question ("when exactly locks are
placed") - in thr_lock() function, inside MySQL lock manager.
It doesn't explain, though, how concurrent inserts are implemented in
MyISAM :)
Regards / Mit vielen Grüssen,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ Principal Software Developer
/_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Radlkoferstr. 2, D-81373 München
<___/ Geschäftsführer: Kaj Arnö - HRB
München 162140
