存储引擎在存储整数时，一般会使用最高位作为标志位，标记存储的整数是正数还是负数（参考），最高位也被称为“most significant bit (MSb)”。通常，最高位为1则表示正数，最高位为0，则表示负数。更进一步的，负数则会通过补码（参考：two’s complement）的方式表示。但是，InnoDB没有使用这种方法。
if (type == DATA_INT) {
/* Store integer data in Innobase in a big-endian format,
sign bit negated if the data is a signed integer. In MySQL,
integers are stored in a little-endian format. */
byte *p = buf + col_len;
for (;;) {
p--;
*p = *mysql_data;
if (p == buf) {
break;
}
mysql_data++;
}
if (!(dtype->prtype & DATA_UNSIGNED)) {
*buf ^= 128;
}
ptr = buf;
buf += col_len;
这段代码中，先将字节序做了颠倒（从最高字节位开始，逐个字节进行拷贝存储），即将 MySQL 层面的小端（little-endian）转化为了InnoDB层面的（big-endian）存储。而后，再对最高位进行了一次翻转，即这里的：*buf ^= 128操作。
mysql> show global variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)
mysql> show session variables like '%iso%';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)
准备表结构
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id int unsigned,
nick varchar(32),
age int,
primary key (id)
)
mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int unsigned | NO | PRI | NULL | |
| nick | varchar(32) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
构建锁等待
在两个会话中，按从上到下，执行下述的SQL：
时间顺序
Session A
Session B
1
START TRANSACTION;
2
INSERT INTO t1 VALUES ( 1, "a",12 );
3
START TRANSACTION;
4
INSERT INTO t1 VALUES ( 1, "x",23 );
这时候，Session B会陷入等待。
观察锁信息
通过 SHOW INNODB STATUS 观察
此时查看 InnoDB 锁信息，则有如下数据：
---TRANSACTION 10094, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 84, OS thread handle 140453961758272, query id 9262 10.88.0.1 sysb update
INSERT INTO t1 VALUES ( 1, "x",23 )
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `sysbenchdb`.`t1` trx id 10094 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00000000276d; asc 'm;;
2: len 7; hex 810000008d0110; asc ;;
3: len 1; hex 61; asc a;;
4: len 4; hex 8000000c; asc ;;
------------------
---TRANSACTION 10093, ACTIVE 23 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 83, OS thread handle 140454159017536, query id 9260 10.88.0.1 sysb
在 SHOW ENGINE INNODB STATUS\G中仅打印了处于等待授予状态的锁信息，即这里仅打印了事务10094的等待的锁详情。
详解锁信息
这里详细看看其中的内容：
RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `sysbenchdb`.`t1` trx id 10094 lock mode S locks rec but not gap waiting
“RECORD LOCKS”
这是一个记录锁
space id 27 page no 4
该记录处于物理位置，包括页面编号，以及页面所处的物理文件编号
n bits 72
该页面有72个记录对应标记位
index PRIMARY of table sysbenchdb.t1
对应表
trx id 10094
所在的事务 ID
lock mode S
锁类型，为 S ，即共享锁
locks rec but not gap
这是一个简单记录锁，无需对记录前的“间隙”加锁
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00000000276d; asc 'm;;
2: len 7; hex 810000008d0110; asc ;;
3: len 1; hex 61; asc a;;
4: len 4; hex 8000000c; asc ;;
# ls -lah
total 2.4G
drwxr-x--- 2 mysql mysql 4.0K Mar 1 14:04 .
drwxr-x--x 11 mysql mysql 4.0K Mar 1 14:02 ..
-rw-r----- 1 mysql mysql 240M Mar 1 14:04 sbtest10.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:00 sbtest1.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:01 sbtest2.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:01 sbtest3.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:02 sbtest4.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:02 sbtest5.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:03 sbtest6.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:03 sbtest7.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:03 sbtest8.ibd
-rw-r----- 1 mysql mysql 240M Mar 1 14:04 sbtest9.ibd
