Documentation Home
MySQL 9.1 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.4Mb
PDF (A4) - 40.5Mb
Man Pages (TGZ) - 259.5Kb
Man Pages (Zip) - 366.7Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


29.12.3.4 The rwlock_instances Table

Therwlock_instances table lists all therwlock (read write lock) instances seen by the Performance Schema while the server executes. Anrwlock is a synchronization mechanism used in the code to enforce that threads at a given time can have access to some common resource following certain rules. The resource is said to be“protected” by therwlock. The access is either shared (many threads can have a read lock at the same time), exclusive (only one thread can have a write lock at a given time), or shared-exclusive (a thread can have a write lock while permitting inconsistent reads by other threads). Shared-exclusive access is otherwise known as ansxlock and optimizes concurrency and improves scalability for read-write workloads.

Depending on how many threads are requesting a lock, and the nature of the locks requested, access can be either granted in shared mode, exclusive mode, shared-exclusive mode or not granted at all, waiting for other threads to finish first.

Therwlock_instances table has these columns:

  • NAME

    The instrument name associated with the lock.

  • OBJECT_INSTANCE_BEGIN

    The address in memory of the instrumented lock.

  • WRITE_LOCKED_BY_THREAD_ID

    When a thread currently has anrwlock locked in exclusive (write) mode,WRITE_LOCKED_BY_THREAD_ID is theTHREAD_ID of the locking thread, otherwise it isNULL.

  • READ_LOCKED_BY_COUNT

    When a thread currently has anrwlock locked in shared (read) mode,READ_LOCKED_BY_COUNT is incremented by 1. This is a counter only, so it cannot be used directly to find which thread holds a read lock, but it can be used to see whether there is a read contention on anrwlock, and see how many readers are currently active.

Therwlock_instances table has these indexes:

  • Primary key on (OBJECT_INSTANCE_BEGIN)

  • Index on (NAME)

  • Index on (WRITE_LOCKED_BY_THREAD_ID)

TRUNCATE TABLE is not permitted for therwlock_instances table.

By performing queries on both of the following tables, a monitoring application or a DBA may detect some bottlenecks or deadlocks between threads that involve locks:

There is a limitation: Therwlock_instances can be used only to identify the thread holding a write lock, but not the threads holding a read lock.