OK, so we’re going to get pretty down-and-dirty in the C source code that handles concurrent inserts… you’ve been warned. 🙂 For background on the MyISAM concurrent insert functionality, see my previous article. Before I get going, I’d like to give a big shout out and thank you to Ingo Strüwing and Sergei Golubchik, whose comments and insight on the MySQL internals mailing list helped me keep from exploding while digging through the source code. Ingo and Serg are great resources that any developer looking to better understand the MySQL sources should be grateful to have around.

The way I approached delving into the concurrent insert functionality was actually quite different from the way I looked at the overall MySQL server architecture in Pro MySQL. When writing the book, I relied heavily on the doxygenized sources of the 5.0.2 source tree, and would follow function and method calls from one class to another, going into the source files for more information on how each function interacted.

For this exercise, I, perhaps mistakenly, thought “I know the main way that threads handle incoming connection requests, and I know that concurrent insert happens in MyISAM, so let’s just go into the MyISAM storage source code and dig around.”. The strategy worked. At first. And then I hit the brick wall known as the internal thread lock managing subsystem. The whole experience can be summed up like so:

1. Jay arrives at a gigantic hedge-maze armed with a large bag of peanuts
2. Jay happily proceeds into the maze intent on finding the bejewelled MyISAM concurrent insert sceptre
3. Jay is not worried about getting lost, because each ten steps, he places a peanut from his bag on the ground
4. Jay wanders around the maze, dropping peanuts and taking notes on various pieces of the maze.
5. All of a sudden Jay realizes an elephant has also come into the maze and summarily eaten the peanuts he has been laying down
6. Jay throws his hands up in despair, waiting to die in said horrible maze
7. Time elapses
8. Two winged creatures named Ingo and Serg rescue Jay from amidst the maze and bring him to the Land of /mysys/thd_lock.c

[Giuseppe, feel free to have your cartoonist draw something illustrating the above choose-your-own-adventure]

Geek Details of Concurrent Insert for MyISAM

Here are the topics and areas of the MySQL code that we’ll be looking at in a discussion on concurrent inserts with MyISAM. The length of the article eventually just became unwieldy, so I have divided it into two articles with various parts:

• In the beginning, there was thr_lock()
• The structure of the .MYD file for MyISAM tables (next article)
• The .MYI Index File Header and Important MyISAM Structs (next article)
• The *write_record function pointers (next article)

In the beginning, there was thr_lock()

In order to properly understand concurrent insert, we must first visit the magical land of the internal MySQL thread locking subsystem. This subsystem is contained in the following source files:

• /include/thr_lock.h
• /mysys/thr_lock.c
• a tiny part of /sql/sql_class.h
• some parts of /include/my_pthread.h

As many of you know, in multi-threaded programming, threads generally modify their own internal data, known as thread-specific data (TSD) or thread-local storage. This data is guaranteed to be local to the thread, and so a thread can modify it without worrying about other threads interfering with its own data store.

However, when threads need access to a shared resource, such as a file or a table share instance (more below), some mechanism must exist to ensure that threads can modify the resource in certain ways, during which other threads have no or limited access to the shared resource. The mechanism that MySQL uses to synchronize these concurrent requests by many threads for a shared resource is the thread locking subsystem.

When connection requests are received by MySQL and a connection thread object (the infamous THD class) is allocated (or popped off the thread_cache), a few things happen (like ACL and some query cache stuff), and then, in general, each SQL request ends up executing the mysql_execute_command() function in /sql/sql_parse.cc. I believe in Pro MySQL I called this function “the biggest switch statement the world has ever seen.” Essentially, the function is just one giant switch that sends control to a more specialized function which handles the specific request. In the case of concurrent inserts, that request would be mysql_insert(), which can be found in /sql/sql_insert.cc.

“But, Jay!”, you ask, “I thought you said in the beginning there was thr_lock()? Indeed, I did. We’re getting to that in one second. But, before we do, there is a somewhat long series of function calls that get us into the thread locking subsystem. To help us navigate, I shall include Ingo’s response from an inquiry I had on the internals@ mailing list:

Most statements call open_and_lock_tables() near their beginning.
Sometimes this is done in a top-level function. In our example this is
sql_insert.cc:mysql_insert().

sql_base.cc:open_and_lock_tables() is quite trivial. Dive into
sql_base.cc:lock_tables(). Here you will find (I hope) some code that
converts the table list into an array ('start'). It then calls
mysql_lock_tables().

lock.cc:mysql_lock_tables() first calls lock_external(). This is where
the engines are called to prepare for a table lock. For MyISAM,
mi_locking() is called, where a file locking can take place, if external
locking is enabled.

lock.cc:mysql_lock_tables() then calls thr_multi_lock().

mysys/thr_lock.c:thr_multi_lock() is the real fancy stuff. Or should I
better point to thr_lock(), called by thr_multi_lock() for each table?

mysys/thr_lock.c:thr_lock() handles all the cases of distinct lock
types, has precedence rules, maintains wait queues, etc... Enjoy.  :) 

Through this function all table locking is done. It is responsible for
letting readers through when a concurrent insert is going on, but pushes
other writers in a wait queue. Or lets one concurrent insert through
when only readers are on the table.

If you’re a visual kind of person, here is a graphic of a simplified call-graph that Ingo described:

One other important function that we’re not going to discuss much is get_lock_data(), from /sql/lock.cc. Suffice it to say that get_lock_data() returns a pointer to a MYSQL_LOCK struct, which has a member variable an array of pointers to THR_LOCK_DATA structs that is passed to the thr_multi_lock() function and eventually to the thr_lock() function.

Lost yet? 🙂 Don’t worry, I’m sure it will become vastly more clear as your eyes begin to glaze over in the thread locking subsystem, which we’ll look at now.

The st_mysql_lock, st_thr_lock_data, and st_thr_lock structs

The st_mysql_lock struct, typedef’d as MYSQL_LOCK in /sql/sql_class.h for some reason, is a simple container for the more important lock structures. It contains an array of pointers to TABLE structs called tables, two counters — table_count and lock_count — and an array of pointers to THR_LOCK_DATA structs called locks:

typedef struct st_mysql_lock
{
  TABLE **table;
  uint table_count,lock_count;
  THR_LOCK_DATA **locks;
} MYSQL_LOCK; 

By far, the most interesting, for us at least, part of the MYSQL_LOCK definition is the array of THR_LOCK_DATA structs, which is our first real glimpse into the weird world of the thread lock management subsystem. Here is the definition of this struct, from /include/thr_lock.h:

typedef struct st_thr_lock_data {
  THR_LOCK_OWNER *owner;
  struct st_thr_lock_data *next,**prev;
  struct st_thr_lock *lock;
  pthread_cond_t *cond;
  enum thr_lock_type type;
  void *status_param;			/* Param to status functions */
  void *debug_print_param;
} THR_LOCK_DATA; 

It should be somewhat obvious that the next and prev member variables make the st_thr_lock_data struct into a self-contained doubly-linked list. This allows an instance of THR_LOCK_DATA to be traversable and sortable.

Secondly, note the member variable lock, which is a pointer to a st_thr_lock, which we look at in a moment.

The cond member variable comes into play during wait cycles for lock requests.

The last member variable of importance is the type variable which is a value of an enum thr_lock_type, which we’ll look at in the next section. The remaining member variables are safely ignored for the purpose of this discussion.

The st_thr_lock struct is the final noteworthy structure in the thread lock management subsystem we’ll look at the definition of. Here it is, also from /include/thr_lock.h:

typedef struct st_thr_lock {
  LIST list;
  pthread_mutex_t mutex;
  struct st_lock_list read_wait;
  struct st_lock_list read;
  struct st_lock_list write_wait;
  struct st_lock_list write;
  /* write_lock_count is incremented for write locks and reset on read locks */
  ulong write_lock_count;
  uint read_no_write_count;
  void (*get_status)(void*, int);	/* When one gets a lock */
  void (*copy_status)(void*,void*);
  void (*update_status)(void*);		/* Before release of write */
  void (*restore_status)(void*);         /* Before release of read */
  my_bool (*check_status)(void *);
} THR_LOCK; 

Let’s walk through the THR_LOCK struct’s member variables. The first member, list, is a pointer to the global list of threads involved in locking. We won’t discuss it much further; it’s used internally to the thread management system and has little to do with our discussion of concurrent insert.

The second member is the actual mutex that accomplishes the atomic locking of the resource.

Next are a series of member variables — read_wait, read, write_wait, and write— all of type struct st_lock_list. This struct is a simple FIFO stack that represents locks for reading or writing, and locks that are waiting to read or write. These are the data structures that are used to “queue up” threads that read or write to the locked resource.

After two counter variables, write_lock_count and read_no_write_count, there are five function pointers that serve as a conduit to allow different storage engines to implement different lock strategies and rules. These function pointers are covered in more detail in the second part of this article series, where you’ll see the MyISAM function pointer prototypes which implement the lock grants for concurrent inserts.

The TL_ lock types

In /include/thr_lock.h you can see the definitions for an enum called thr_lock_type:

enum thr_lock_type { TL_IGNORE=-1,
		     TL_UNLOCK,			/* UNLOCK ANY LOCK */
		     TL_READ,			/* Read lock */
		     TL_READ_WITH_SHARED_LOCKS,
		     /* High prior. than TL_WRITE. Allow concurrent insert */
		     TL_READ_HIGH_PRIORITY,
		     /* READ, Don't allow concurrent insert */
		     TL_READ_NO_INSERT,
		     /* 
			Write lock, but allow other threads to read / write.
			Used by BDB tables in MySQL to mark that someone is
			reading/writing to the table.
		      */
		     TL_WRITE_ALLOW_WRITE,
		     /*
			Write lock, but allow other threads to read.
			Used by ALTER TABLE in MySQL to allow readers
			to use the table until ALTER TABLE is finished.
		     */
		     TL_WRITE_ALLOW_READ,
		     /*
		       WRITE lock used by concurrent insert. Will allow
		       READ, if one could use concurrent insert on table.
		     */
		     TL_WRITE_CONCURRENT_INSERT,
		     /* Write used by INSERT DELAYED.  Allows READ locks */
		     TL_WRITE_DELAYED,
                     /* 
                       parser only! Late bound low_priority flag. 
                       At open_tables() becomes thd->update_lock_default.
                     */
                     TL_WRITE_DEFAULT,
		     /* WRITE lock that has lower priority than TL_READ */
		     TL_WRITE_LOW_PRIORITY,
		     /* Normal WRITE lock */
		     TL_WRITE,
		     /* Abort new lock request with an error */
		     TL_WRITE_ONLY}; 

Remember that the THR_LOCK_DATA struct has a member variable called type, which is of type enum thr_lock_type. The above definition contains the possible values that the lock request represented by the THR_LOCK_DATA instance can have.

I’ll give you one guess which lock type comes into play with concurrent inserts. Yep, TL_WRITE_CONCURRENT_INSERT. Also note that the enum values are intentionally listed in increasing order of lock priority within the READ and WRITE groups. In other words, a lock of lock_type TL_WRITE has a greater priority than a lock of type TL_WRITE_DELAYED has a greater priority than a lock of type TL_WRITE_CONCURRENT_INSERT. This is very important.

Summarizing the locking structures

Before we get into the locking implementation, first review how all the structures noted above interact.

Remember that there is a global list of threads involved in locking. In this list there is a list of THR_LOCK objects that correspond to a specific table. Now, this THR_LOCK structure contains linked lists of THR_LOCK_DATA objects which represent an individual lock request made against the table. Each lock request (THR_LOCK_DATA object) is of a specific type: a value of type enum thr_lock_type.

Finally, each THR_LOCK object contains queues of these lock requests in various states: reading, writing, waiting to read or waiting to write.

And finally… the thr_lock() function

Let’s see how these lock structs and lock types are used in the locking mechanism of thr_lock(). I’ve split the very long function into parts, and we’ll discuss each in turn. I’ve also removed the debug and NOOP calls for brevity.

Setup and Mutex Lock

enum enum_thr_lock_result
thr_lock(THR_LOCK_DATA *data, THR_LOCK_OWNER *owner,
         enum thr_lock_type lock_type)
{
  THR_LOCK *lock=data->lock;
  enum enum_thr_lock_result result= THR_LOCK_SUCCESS;
  struct st_lock_list *wait_queue;
  THR_LOCK_DATA *lock_owner;
 
  data->next=0;
  data->cond=0;					/* safety */
  data->type=lock_type;
  data->owner= owner;                           /* Must be reset ! */
  VOID(pthread_mutex_lock(&lock->mutex)); 

In the first few lines of thr_lock(), some local variable initialization is done, setting the lock type and owner of the supplied THR_LOCK_DATA instance. Then, importantly, the mutex that controls this table’s THR_LOCK — &lock->mutex — is locked with pthread_mutex_lock. For more information on pthread_lock_mutex, and the implementation of the POSIX threading (and emulation) in MySQL, see /include/my_pthread.h and /mysys/my_pthread.c. Have fun!

Handling READ requests

  if ((int) lock_type <= (int) TL_READ_NO_INSERT)
  {
    /* Request for READ lock */
    if (lock->write.data)
    {
      /* We can allow a read lock even if there is already a write lock
	 on the table in one the following cases:
	 - This thread alread have a write lock on the table
	 - The write lock is TL_WRITE_ALLOW_READ or TL_WRITE_DELAYED
           and the read lock is TL_READ_HIGH_PRIORITY or TL_READ
         - The write lock is TL_WRITE_CONCURRENT_INSERT or TL_WRITE_ALLOW_WRITE
	   and the read lock is not TL_READ_NO_INSERT
      */
 
      if (thr_lock_owner_equal(data->owner, lock->write.data->owner) ||
	  (lock->write.data->type <= TL_WRITE_DELAYED &&
	   (((int) lock_type <= (int) TL_READ_HIGH_PRIORITY) ||
	    (lock->write.data->type != TL_WRITE_CONCURRENT_INSERT &&
	     lock->write.data->type != TL_WRITE_ALLOW_READ))))
      {						/* Already got a write lock */
	(*lock->read.last)=data;		/* Add to running FIFO */
	data->prev=lock->read.last;
	lock->read.last= &data->next;
	if (lock_type == TL_READ_NO_INSERT)
	  lock->read_no_write_count++;
	if (lock->get_status)
	  (*lock->get_status)(data->status_param, 0);
	statistic_increment(locks_immediate,&THR_LOCK_lock);
	goto end;
      }
      if (lock->write.data->type == TL_WRITE_ONLY)
      {
	/* We are not allowed to get a READ lock in this case */
	data->type=TL_UNLOCK;
        result= THR_LOCK_ABORTED;               /* Can't wait for this one */
	goto end;
      }
    }
    else if (!lock->write_wait.data ||
	     lock->write_wait.data->type <= TL_WRITE_LOW_PRIORITY ||
	     lock_type == TL_READ_HIGH_PRIORITY ||
	     have_old_read_lock(lock->read.data, data->owner))
    {						/* No important write-locks */
      (*lock->read.last)=data;			/* Add to running FIFO */
      data->prev=lock->read.last;
      lock->read.last= &data->next;
      if (lock->get_status)
	(*lock->get_status)(data->status_param, 0);
      if (lock_type == TL_READ_NO_INSERT)
	lock->read_no_write_count++;
      statistic_increment(locks_immediate,&THR_LOCK_lock);
      goto end;
    }
    /*
      We're here if there is an active write lock or no write
      lock but a high priority write waiting in the write_wait queue.
      In the latter case we should yield the lock to the writer.
    */
    wait_queue= &lock->read_wait;
  } 

If we break down the various conditionals in the code above, things actually start to make (a bit) of sense. The conditional in line 1 checks that the lock type being requested is less than or equal to TL_READ_NO_INSERT. If true, that just means that this request is a read request, as noted by the comment afterwards.

The conditional in line 3 will return true if the THR_LOCK‘s queue of running threads with write locks is set (to a pointer to the first write lock request). If so, that means that there is at least one thread that has a write lock on this table.

The conditional on lines 15-19 handles the scenarios outlined in the comment on lines 6-13:

• When this specific thread is the one with the write lock
• The write lock is TL_WRITE_ALLOW_READ or TL_WRITE_DELAYED
  and the read lock is TL_READ_HIGH_PRIORITY or TL_READ
• The write lock is TL_WRITE_CONCURRENT_INSERT or TL_WRITE_ALLOW_WRITE
  and the read lock is not TL_READ_NO_INSERT

Lines 21-29 handle the above scenarios. In these cases, the currently requesting thread is added into the read FIFO queue of the THR_LOCK for this table (lines 21-23). Now, in lines 26 and 27 you see the first use of the function pointers I described in the section above on the definition of THR_LOCK. Line 26 first checks to see if there is a valid function that the change_status function pointer points to. If so, that function pointer is called. We’ll take a look at the MyISAM storage engine’s implementation of the change_status function pointer —mi_change_status()— in the next article, where you will see it’s effect for concurrent inserts.

Line 28 increments the global locks_imediate status variable, and then we goto end, which simply returns a successful thread lock result to the caller (thr_multi_lock).

Lines 31-37 handle cases where there is a write lock on the table of type TL_WRITE_ONLY. The function ends, aborting the read request.

Lines 39-53 handle the cases where there are no currently running write locking threads, but there are write locking threads in the waiting queue for this table and the lock type for this lock request is a read lock. The conditional on lines 39-42 is true in the following cases:

• There isn’t anything at all in the THR_LOCK.write_wait queue
• The only write requests in the write_wait queue have a TL_WRITE_LOW_PRIORITY lock type or lower
• The currently requested lock type for this table is TL_READ_HIGH_PRIORITY
• The thread first on the currently reading queue is the same as the requesting thread

In each of the above cases, a read lock is granted to this lock request and the lock request is added into the THR_LOCK.read queue and the locks_immediate status variable is incremented (lines 44-52).

For any request that does not meet the conditionals above and is a read request, the lock request is placed in the THR_LOCK.read_wait queue (line 59)

Handling WRITE requests

  else						/* Request for WRITE lock */
  {
    if (lock_type == TL_WRITE_DELAYED)
    {
      if (lock->write.data && lock->write.data->type == TL_WRITE_ONLY)
      {
	data->type=TL_UNLOCK;
        result= THR_LOCK_ABORTED;               /* Can't wait for this one */
	goto end;
      }
      /*
	if there is a TL_WRITE_ALLOW_READ lock, we have to wait for a lock
	(TL_WRITE_ALLOW_READ is used for ALTER TABLE in MySQL)
      */
      if ((!lock->write.data ||
	   lock->write.data->type != TL_WRITE_ALLOW_READ) &&
	  !have_specific_lock(lock->write_wait.data,TL_WRITE_ALLOW_READ) &&
	  (lock->write.data || lock->read.data))
      {
	/* Add delayed write lock to write_wait queue, and return at once */
	(*lock->write_wait.last)=data;
	data->prev=lock->write_wait.last;
	lock->write_wait.last= &data->next;
	data->cond=get_cond();
        /*
          We don't have to do get_status here as we will do it when we change
          the delayed lock to a real write lock
        */
	statistic_increment(locks_immediate,&THR_LOCK_lock);
	goto end;
      }
    }
    else if (lock_type == TL_WRITE_CONCURRENT_INSERT && ! lock->check_status)
      data->type=lock_type= thr_upgraded_concurrent_insert_lock;
 
    if (lock->write.data)			/* If there is a write lock */
    {
      if (lock->write.data->type == TL_WRITE_ONLY)
      {
	/* We are not allowed to get a lock in this case */
	data->type=TL_UNLOCK;
        result= THR_LOCK_ABORTED;               /* Can't wait for this one */
	goto end;
      }
 
      /*
	The following test will not work if the old lock was a
	TL_WRITE_ALLOW_WRITE, TL_WRITE_ALLOW_READ or TL_WRITE_DELAYED in
	the same thread, but this will never happen within MySQL.
      */
      if (thr_lock_owner_equal(data->owner, lock->write.data->owner) ||
	  (lock_type == TL_WRITE_ALLOW_WRITE &&
	   !lock->write_wait.data &&
	   lock->write.data->type == TL_WRITE_ALLOW_WRITE))
      {
	/*
          We have already got a write lock or all locks are
          TL_WRITE_ALLOW_WRITE
        */
	(*lock->write.last)=data;	/* Add to running fifo */
	data->prev=lock->write.last;
	lock->write.last= &data->next;
	if (data->lock->get_status)
	  (*data->lock->get_status)(data->status_param, 0);
	statistic_increment(locks_immediate,&THR_LOCK_lock);
	goto end;
      }
    }
    else
    {
      if (!lock->write_wait.data)
      {						/* no scheduled write locks */
        my_bool concurrent_insert= 0;
	if (lock_type == TL_WRITE_CONCURRENT_INSERT)
        {
          concurrent_insert= 1;
          if ((*lock->check_status)(data->status_param))
          {
            concurrent_insert= 0;
            data->type=lock_type= thr_upgraded_concurrent_insert_lock;
          }
        }
 
	if (!lock->read.data ||
	    (lock_type <= TL_WRITE_DELAYED &&
	     ((lock_type != TL_WRITE_CONCURRENT_INSERT &&
	       lock_type != TL_WRITE_ALLOW_WRITE) ||
	      !lock->read_no_write_count)))
	{
	  (*lock->write.last)=data;		/* Add as current write lock */
	  data->prev=lock->write.last;
	  lock->write.last= &data->next;
	  if (data->lock->get_status)
	    (*data->lock->get_status)(data->status_param, concurrent_insert);
	  statistic_increment(locks_immediate,&THR_LOCK_lock);
	  goto end;
	}
      }
    }
    wait_queue= &lock->write_wait;
  } 

Again, the above code makes more sense if you break it into a set of conditionals which handle the various write lock request rules.

The first conditional block (lines 3-32) handles write lock requests on this table of type TL_WRITE_DELAYED. Directly after the test on the lock type, there is a conditional block which aborts if the current lock type of currently writing threads is of type TL_WRITE_ONLY (lines 5-10). Lines 15-31 handles the following scenario for current TL_WRITE_DELAYED requests:

• There are no current threads with a write lock on this table OR the current write locks are NOT TL_WRITE_ALLOW_READ
• AND there are no requests in the THR_LOCK.write_wait queue of type TL_WRITE_ALLOW_READ

If the above scenario is true, then the current lock request is added to the write_wait queue and we return control to thr_multi_lock() (lines 20-30).

Lines 33-34 handle the situation where the engine supports concurrent insert, but does not implement the required function pointer for checking the status of the table’s concurrent insert ability.

Lines 36-68 handle situations where there is already a currently writing thread on this table. lines 38-44 handle the special TL_WRITE_ONLY scenario. Lines 46-67 handle a somewhat pedantic (and not realistic) scenario that I won’t go into here.

Lines 69-100 handle scenarios where there is not an currently writing thread on this table and is the more interesting of the conditionals in this section of code.

The bulk of this code block is contained within a conditional that tests if there are any write requests waiting in the THR_LOCK.write_wait queue (line 71). If there are any waiting write requests, the current write request is placed at the end of the write_wait queue (line 100). If there are no waiting write requests, lines 73-98 are executed.

Lines 73-82 handle the scenario where we’ve got no waiting write requests and the currently requested write lock is of type TL_WRITE_CONCURRENT_INSERT. If the check_status function pointer of the THR_LOCK does not return 0, then the local concurrent_insert variable is set to 0 from 1 (see lines 73-79).

Next up is a conditional (lines 84-88) which evaluates true for either of the following scenarios:

• When we don’t have any currently reading threads on this table at all
• When the lock type requested is TL_WRITE_ALLOW_READ or TL_WRITE_DELAYED OR the read_no_write_count counter of the THR_LOCK is zero

The last bullet point above is an interesting one, as you will note that the read_no_write_count counter is only incremented when a read request is granted with a lock type of TL_READ_NO_INSERT. This means that, under normal operations, a write lock request of type TL_WRITE_CONCURRENT_INSERT will actually be granted in this conditional block (lines 90-96).

Deadlock detection and finalization

  /*
    Try to detect a trivial deadlock when using cursors: attempt to
    lock a table that is already locked by an open cursor within the
    same connection. lock_owner can be zero if we succumbed to a high
    priority writer in the write_wait queue.
  */
  lock_owner= lock->read.data ? lock->read.data : lock->write.data;
  if (lock_owner && lock_owner->owner->info == owner->info)
  {
    result= THR_LOCK_DEADLOCK;
    goto end;
  }
  /* Can't get lock yet;  Wait for it */
  DBUG_RETURN(wait_for_lock(wait_queue, data, 0));
end:
  pthread_mutex_unlock(&lock->mutex);
	DBUG_RETURN(result);
} 

The final code section just does a simple attempt at deadlock detection (lines 1-14) or, at the end label, unlocks the THR_LOCK.mutex mutex lock and returns the result of the lock request to thr_multi_lock().

Wrap-up discussion on thr_lock()

Well, if you’re still following along, congrats! We’ve covered some fairly complex code in the internal MYSQL thread locking subsystem. We covered the relevant structures involved and deconstructed the long thr_lock() function which acts as the arbiter of lock requests on tables within a MySQL database. In the next article, I’ll tie in the MyISAM storage engine and show you how the storage engine’s overrides of the THR_LOCK‘s function pointers complement the lock request rules and routing in thr_lock(). I’m about 90% done with the next article — mostly because much of it was written before running into the wall of thread lock management — so look for it being published tomorrow… 🙂

---

On a final note, if you did happen to make it through this article with brain cells to spare, and you’re looking for a job, why not consider applying at our favorite company… MySQL has a ton of jobs listed. 🙂