MySQL信号量等待日志阅读

--Thread 140477271320320 has waited at ibuf0ibuf.cc line 3439 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x7fc5a5faa520 created in file buf0buf.cc line 1460
a writer (thread id 140477635114752) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: fffffffff0000000
Last time read locked in file ibuf0ibuf.cc line 4552
Last time write locked in file /dba/app/mysql_home/5.7.19/storage/innobase/ibuf/ibuf0ibuf.cc line 406
2020-11-25T09:33:15.302783+08:00 0 [Warning] InnoDB: A long semaphore wait:
--Thread 140477265372928 has waited at btr0cur.cc line 976 for 241.00 seconds the semaphore:
SX-lock on RW-latch at 0x7fc3189b57b0 created in file dict0dict.cc line 2687
a writer (thread id 140477291595520) has reserved it in mode  SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0cur.cc line 1008
Last time write locked in file /dba/app/mysql_home/5.7.19/storage/innobase/btr/btr0cur.cc line 976

/* We decrement lock_word by X_LOCK_DECR for each x_lock. It is also the
start value for the lock_word, meaning that it limits the maximum number
of concurrent read locks before the rw_lock breaks. */
/* We decrement lock_word by X_LOCK_HALF_DECR for sx_lock. */
#define X_LOCK_DECR		0x20000000
#define X_LOCK_HALF_DECR	0x10000000

rw_lock_x_lock_low(rw_lock_t*  lock, ulint pass, const char* file_name, ulint line) {

  // 如果lock_word>X_LOCK_HALF_DECR，尝试将lock_word减少X_LOCK_DECR，代表了X锁只能添加一次
  // 如果成功，则至少预定自己为下一个写锁的持有者，返回true，否则返回false
  if (rw_lock_lock_word_decr(lock, X_LOCK_DECR, X_LOCK_HALF_DECR)) {
  
    // 预定自己为下一个写锁持有者，此时lock_word<=0，last_x_file_name:last_x_line 为上一个写锁持有者的上锁位置
    // 将自己的线程标识写入writer_thread，
    rw_lock_set_writer_id_and_recursion_flag(lock, !pass);)

    // 如果lock_word<0，说明有线程持有读锁，必须等待读锁释放
    // 阻塞直到 lock_word==0, 
    rw_lock_x_lock_wait(lock, pass, 0, file_name, line);

  } else {
    ......
  }
  
  // 成功获得写锁，last_x_file_name:last_x_line指向加锁的位置
  lock->last_x_file_name = file_name;
  lock->last_x_line = (unsigned int) line;

  return true;
}

rw_lock_lock_word_decr(
/*===================*/
	rw_lock_t*	lock,		/*!< in/out: rw-lock */
	ulint		amount,		/*!< in: amount to decrement */
	lint		threshold)	/*!< in: threshold of judgement */
{
#ifdef INNODB_RW_LOCKS_USE_ATOMICS
	lint local_lock_word;

	os_rmb;
	local_lock_word = lock->lock_word;
	while (local_lock_word > threshold) { 
		if (os_compare_and_swap_lint(&lock->lock_word,
					     local_lock_word,
					     local_lock_word - amount)) {
			return(true);
		}
		local_lock_word = lock->lock_word;
	}
	return(false);
#else /* INNODB_RW_LOCKS_USE_ATOMICS */
	bool success = false;
	mutex_enter(&(lock->mutex));
	if (lock->lock_word > threshold) {
		lock->lock_word -= amount;
		success = true;
	}
	mutex_exit(&(lock->mutex));
	return(success);
#endif /* INNODB_RW_LOCKS_USE_ATOMICS */
}

while (local_lock_word > threshold) { 
		if (os_compare_and_swap_lint(&lock->lock_word,
					     local_lock_word,
					     local_lock_word - amount)) {
			return(true);
		}
		local_lock_word = lock->lock_word;
	}
经过上面运算后lock_word = 0 即代表加X锁成功

Total number of rw-locks 393385
OS WAIT ARRAY INFO: reservation count 585479
OS WAIT ARRAY INFO: signal count 112559
RW-shared spins 0, rounds 530324, OS waits 385606
RW-excl spins 0, rounds 1265658, OS waits 41898
RW-sx spins 67610, rounds 2015448, OS waits 66228
Spin rounds per wait: 530324.00 RW-shared, 1265658.00 RW-excl, 29.81 RW-sx

* 如果可用，则旋转周期退出。步骤3c：再次尝试获取所需的锁。如果成功，则返回。（spins=1, rounds=M (M <= N), os-waits=0）如果失败，并且有一些待处理的回合（max = innodb_sync_spin_loops），则继续旋转。（如何中断循环并导致锁定失败。注意：并行由多个线程查看该锁。虽然在尝试获取该锁时，多个线程已收到有关锁可用性的信号，但其他线程却接过了该锁因此，该线程现在返回重试）。

* 所述线程通过在所述阵列中保留插槽来注册自身。
* 在开始等待之前，请再次尝试查看该锁是否可用。（因为预订可能很耗时，并且锁可能同时可用）。
* 如果仍然没有锁，则等待同步阵列基础结构发信号通知该线程。
* 此等待称为OS等待，进入此循环现在将导致OS等待计数增加。步骤3f：说该线程由同步阵列基础结构发信号通知等待事件。重试以获得所需的锁。如果成功，则返回。（spins=1, rounds=N, os-waits=1）如果失败，则整个循环从旋转逻辑重新启动（返回到第3步，并将回合计数重新初始化为0）。注意：spin次数不会重新增加。

RW-shared spins 338969, rounds 20447615, OS waits 592941
RW-excl spins 50582, rounds 1502625, OS waits 56124
RW-sx spins 12583, rounds 360973, OS waits 10484
Spin rounds per wait: 60.32 RW-shared, 29.71 RW-excl, 28.69 RW-sx

RW-shared spins 35943, rounds 777178, OS waits 19051
RW-excl spins 4269, rounds 121796, OS waits 4164
RW-sx spins 13407, rounds 321954, OS waits 7346
Spin rounds per wait: 21.62 RW-shared, 28.53 RW-excl, 24.01 RW-sx

RW-shared spins 39578, rounds 424553, OS waits 7329
RW-excl spins 5828, rounds 78225, OS waits 1341
RW-sx spins 11666, rounds 67297, OS waits 449
Spin rounds per wait: 10.73 RW-shared, 13.42 RW-excl, 5.77 RW-sx