MySQL :: MySQL 8.4 Reference Manual :: 29.12.3.4 The rwlock_instances Table (original) (raw)

29.12.3.4 The rwlock_instances Table

The rwlock_instances table lists all the rwlock (read write lock) instances seen by the Performance Schema while the server executes. An rwlock 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 the rwlock. 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.

The rwlock_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 an rwlock locked in exclusive (write) mode,WRITE_LOCKED_BY_THREAD_ID is theTHREAD_ID of the locking thread, otherwise it is NULL.
• READ_LOCKED_BY_COUNT
  When a thread currently has an rwlock 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.

The rwlock_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 the rwlock_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:

• events_waits_current, to see what rwlock a thread is waiting for
• rwlock_instances, to see which other thread currently owns anrwlock

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.