MySQL锁系列（七）之 锁算法详解

能学到什么

  1. 隔离级别和锁的关系
  2. 重点讲解在RR隔离级别下的加锁算法逻辑
  3. 重点罗列了比较典型的几种加锁逻辑案例
  4. 对insert的加锁逻辑进行了深度剖析
  5. 实战中剖析加锁的全过程
  6. InnoDB为什么要这样加锁

隔离级别和算法

  • repeatable-read
1. 使用的是next-key locking
2. next-key lock  =  record lock + Gap lock
  • read-committed
1. 使用的是 record lock
2. 当然特殊情况下( purge + unique key )，也会有Gap lock

我们接下来就以RR隔离级别来阐述，因为RC更加简单

  • 锁的通用算法

RR隔离级别

1. 锁是在索引上实现的
2. 假设有一个key，有5条记录， 1，3，5，7，9.  如果where id<5 ， 那么锁住的区间不是（-∞，5），而是(-∞,1],(1,3],(3,5] 多个区间组合而成
3. RR隔离级别使用的是：next-key lock算法，即：锁住 记录本身+区间
4. next-key lock 降级为 record lock的情况
    如果是唯一索引，且查询条件得到的结果集是1条记录（等值，而不是范围），那么会降级为记录锁
    典型的案例：where primary_key = 1 (会降级), 而不是 where primary_key < 10 （由于返回的结果集不仅仅一条，那么不会降级）
5. 上锁，不仅仅对主键索引加锁，还需要对辅助索引加锁，这一点非常重要

锁算法的案例剖析

RR隔离级别

  • 表结构
dba:lc_3> show create table a;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------+
| Table | Create Table
             |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------+
| a     | CREATE TABLE `a` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `idx_b` (`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------+
1 row in set (0.00 sec)

dba:lc_3> select * from a;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+
4 rows in set (0.00 sec)

* 设置RR隔离级别
set tx_isolation = 'repeatable-read';
  • 等值查询，非唯一索引的加锁逻辑
dba:lc_3> begin;
Query OK, 0 rows affected (0.00 sec)

dba:lc_3> select * from a where c=9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 5 |    7 |    9 |   11 |
+---+------+------+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601815 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601815 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601815 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d012a; asc    '  *;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000009; asc     ;;
 5: len 4; hex 8000000b; asc     ;;

RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601815 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000007; asc     ;;

锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock，((7,3),(9,5)] , ((9,5),(11,7)]，解读一下：((7,3),(9,5)] 表示：7是二级索引key，3是对应的主键
    2.这样写不太好懂，所以以后就暂时忽略掉主键这样写： next-key lock = (7,9],(9,11]

对主键索引primary： 加record lock，[5]
  • 等值查询，唯一键的加锁逻辑
dba:lc_3> select * from a where b=9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 7 |    9 |   11 |   13 |
+---+------+------+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601816 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601816 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601816 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0137; asc    '  7;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000d; asc     ;;

锁的结构如下：

对二级索引idx_b：
    1. 加record lock，[9]

对主键索引primary：
    1. 加record lock，[7]
  • >= ，非唯一索引的加锁逻辑
dba:lc_3> select * from a where c>=9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+
2 rows in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601817 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601817 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601817 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d012a; asc    '  *;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000009; asc     ;;
 5: len 4; hex 8000000b; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0137; asc    '  7;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000d; asc     ;;

锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (7,9],(9,11],(11,∞]

对主键索引primary：
    1. 加record lock，[5],[7]
  • >= ，唯一索引的加锁逻辑
dba:lc_3> select * from a where b>=7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+
2 rows in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601820 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601820 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000005; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601820 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d012a; asc    '  *;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000009; asc     ;;
 5: len 4; hex 8000000b; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0137; asc    '  7;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000d; asc     ;;

锁的结构如下：

对二级索引idx_b：
    1. 加next-key lock， (5,7],(7,9],(9,∞]

对主键索引primary：
    1. 加record lock，[5],[7]
  • <= , 非唯一索引的加锁逻辑

dba:lc_3> select * from a where c<=7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
+---+------+------+------+
2 rows in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601822 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601822 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601822 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0110; asc    '   ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d011d; asc    '   ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (-∞,5],(5,7],(7,9]

对主键索引primary：
    1. 加record lock，[1],[3]
  • <= , 唯一索引的加锁逻辑
dba:lc_3> select * from a where b<=5 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
+---+------+------+------+
2 rows in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601823 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601823 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601823 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0110; asc    '   ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d011d; asc    '   ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

锁的结构如下：

对二级索引idx_b：
    1. 加next-key lock， (-∞,3],(3,5],(5,7]

对主键索引primary：
    1. 加record lock，[1],[3]
  • > , 非唯一索引的加锁逻辑
dba:lc_3> select * from a where c>9 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 7 |    9 |   11 |   13 |
+---+------+------+------+
1 row in set (0.00 sec)

RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601825 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601825 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0137; asc    '  7;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000d; asc     ;;

锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (9,11],(11,∞]

对主键索引primary：
    1. 加record lock，[7]
  • > , 唯一索引的加锁逻辑
dba:lc_3> select * from a where b>7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 7 |    9 |   11 |   13 |
+---+------+------+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601826 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601826 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000007; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601826 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0137; asc    '  7;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000d; asc     ;;

锁的结构如下：

对二级索引idx_b：
    1. 加next-key lock， (7,9],(9,∞]

对主键索引primary：
    1. 加record lock，[7]
  • < , 非唯一索引的加锁逻辑
dba:lc_3> select * from a where c<7 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
+---+------+------+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601827 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601827 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601827 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0110; asc    '   ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (-∞,5],(5,7]

对主键索引primary：
    1. 加record lock，[1]
  • < , 唯一索引的加锁逻辑
dba:lc_3> select * from a where b<5 for update;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
+---+------+------+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`a` trx id 133601828 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601828 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601828 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0110; asc    '   ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

锁的结构如下：

对二级索引idx_c：
    1. 加next-key lock， (-∞,3],(3,5]

对主键索引primary：
    1. 加record lock，[1]
  • 总结之前的加锁逻辑
* 如果
1. select * from xx where col <比较运算符> M for update
2. M->next-rec: 表示M的下一条记录
3. M->pre-rec: 表示M的前一条记录

########第一轮总结########

* 等值查询M，非唯一索引的加锁逻辑
    (M->pre-rec,M],(M,M->next-rec]

* 等值查询M，唯一键的加锁逻辑
    [M], next-lock 降级为 record locks

* >= ，非唯一索引的加锁逻辑
    (M->pre_rec,M],(M,M->next-rec]....(∞]

* >= ，唯一索引的加锁逻辑
    (M->pre_rec,M],(M,M->next-rec]....(∞]

* <= , 非唯一索引的加锁逻辑
    (-∞] ... (M,M->next-rec]

* <= , 唯一索引的加锁逻辑
    (-∞] ... (M,M->next-rec]

* > , 非唯一索引的加锁逻辑
     (M,M->next-rec] ... (∞]

* > , 唯一索引的加锁逻辑
     (M,M->next-rec] ... (∞]

* < , 非唯一索引的加锁逻辑
     (-∞] ... (M->rec,M]

* < , 唯一索引的加锁逻辑
     (-∞] ... (M->rec,M]

########第二轮总结合并########

* 等值查询M，非唯一索引的加锁逻辑
    (M->pre-rec,M],(M,M->next-rec]

* 等值查询M，唯一键的加锁逻辑
    [M], next-lock 降级为 record locks
    这里大家还记得之前讲过的通用算法吗：
            next-key lock 降级为 record lock的情况：
                如果是唯一索引，且查询条件得到的结果集是1条记录（等值，而不是范围），那么会降级为记录锁

* >= ，加锁逻辑
    (M->pre_rec,M],(M,M->next-rec]....(∞]

* > ,  加锁逻辑
     (M,M->next-rec] ... (∞]

* <= , 加锁逻辑
    (-∞] ... (M,M->next-rec]

* < , 加锁逻辑
     (-∞] ... (M->rec,M]

########最后的疑问和总结########

1. 疑问： 为什么要对M->next-rec 或者  M->pre-rec ？

1. 回答： 因为为了防止幻读。

insert 操作的加锁逻辑

RR 隔离级别

  • 表结构
dba:lc_3> show create table tb_non_uk;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                           |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_non_uk | CREATE TABLE `tb_non_uk` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id2` (`id_2`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

dba:lc_3> show create table tb_uk;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_uk | CREATE TABLE `tb_uk` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx` (`id_2`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

dba:lc_3> select * from tb_non_uk;
+----+------+
| id | id_2 |
+----+------+
|  1 |  100 |
|  2 |  200 |
+----+------+
2 rows in set (0.00 sec)

dba:lc_3> select * from tb_uk;
+----+------+
| id | id_2 |
+----+------+
|  1 |   10 |
|  2 |   20 |
| 33 |   30 |
+----+------+
3 rows in set (0.00 sec)
  • 普通的insert,insert之前,其他事务没有对next-record加任何锁


dba:lc_3> insert into tb_uk select 100,200;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

锁的结构：

MySQL thread id 11888, OS thread handle 140000862643968, query id 24975 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601936 lock mode IX

没有加任何的锁，除了在表上面加了意向锁之外，这个锁基本上只要访问到表都会加的

难道insert不会加锁吗？显然不是，那是因为加的是隐式类型的锁
  • 有唯一键约束，insert之前，其他事务且对其next-record加了Gap-lock
* session 1:

select * from tb_uk where id_2 >= 30 for update;

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601951 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601951 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601951 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;

锁住： (20,30](30,∞) ， 对30有Gap锁

* session 2:

dba:lc_3> insert into tb_uk select 3,25;
Query OK, 1 row affected (6.30 sec)
Records: 1  Duplicates: 0  Warnings: 0

* session 1:

rollback;

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601952 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601952 lock_mode X locks gap before rec insert intention
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

当session2 插入25的时候，这时候session2 会被卡住。 然后session 2 释放gap lock后，session 1 就持有插入意向锁 lock_mode X locks gap before rec insert intention
  • 有唯一键约束，insert之前，其他事务且对其next-record加了record lock
* session 1:

dba:lc_3> select * from tb_uk where id_2 = 30 for update;
+----+------+
| id | id_2 |
+----+------+
| 33 |   30 |
+----+------+
1 row in set (0.00 sec)

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;

* session 2:

dba:lc_3> insert into tb_uk select 3,25;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

锁结构：

说明有唯一键约束，insert之前，其他事务且对其next-record加了record lock，不会阻塞insert。

此时的insert，也不会产生insert intension lock
  • 有唯一键约束，insert 记录之后，发现原来的表有重复值的情况,

* session 1:

dba:lc_3> select * from tb_uk where id_2 = 30 for update;
+----+------+
| id | id_2 |
+----+------+
| 33 |   30 |
+----+------+
1 row in set (0.00 sec)

dba:lc_3> delete from tb_uk where id_2 = 20;
Query OK, 1 row affected (0.00 sec)

这时候的锁结构如下：

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a97; asc       ;;
 2: len 7; hex 460000403f090b; asc F  @?  ;;
 3: len 4; hex 80000014; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;

对二级索引uniq_idx ：
    1. 加record lock ， [20]，[30]

对主键索引：
    1. 加record lock，[2],[33]

* session 2:

dba:lc_3> insert into tb_uk select 3,20;
...............waiting.................

这时候，我们再来看看锁结构：

TABLE LOCK table `lc_3`.`tb_uk` trx id 133601949 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601949 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

---TRANSACTION 133601943, ACTIVE 490 sec
3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 11889, OS thread handle 140000878618368, query id 25018 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 4; hex 80000021; asc    !;;

RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a97; asc       ;;
 2: len 7; hex 460000403f090b; asc F  @?  ;;
 3: len 4; hex 80000014; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000021; asc    !;;
 1: len 6; hex 000007f69a77; asc      w;;
 2: len 7; hex ad00000d010110; asc        ;;
 3: len 4; hex 8000001e; asc     ;;

info bits 32 表示这条记录已经标记为删除状态

这里面的session 2 ： insert into tb_uk select 3,20; 被阻塞了
因为，这条insert 语句需要对 uniq_idx中的20加lock mode S ， 但是发现session 1 已经对其加了lock_mode X locks rec but not gap，而这条记录被标记为删除状态
所以发生锁等待，因为S lock 和 X lock 冲突
  • 没有唯一键约束,insert之前，其他事务对其next-record加了Gap-lock
* session 1:

dba:lc_3> select * from tb_non_uk where id_2>=100 for update;
+----+------+
| id | id_2 |
+----+------+
|  1 |  100 |
|  2 |  200 |
+----+------+
2 rows in set (0.00 sec)

锁结构：

TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601939 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 302 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a6b; asc      k;;
 2: len 7; hex a500000d360110; asc     6  ;;
 3: len 4; hex 800000c8; asc     ;;

对idx_id2二级索引： (100,200],(200,∞]
对主键索引： [2]

* session 2:

dba:lc_3> insert into tb_non_uk select 3,150;
......waiting.....

---TRANSACTION 133601940, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 11888, OS thread handle 140000862643968, query id 24996 localhost dba executing
insert into tb_non_uk select 3,150
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601940 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

------------------
TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601940 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601940 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

---TRANSACTION 133601939, ACTIVE 311 sec
3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11889, OS thread handle 140000878618368, query id 24994 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601939 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800000c8; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 302 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000007f69a6b; asc      k;;
 2: len 7; hex a500000d360110; asc     6  ;;
 3: len 4; hex 800000c8; asc     ;;

锁结构：
    多了一个插入意向锁 lock_mode X locks gap before rec insert intention
  • 总结Insert 操作的加锁流程
* insert 的流程(没有唯一索引的情况)： insert N

1. 找到大于N的第一条记录M
2. 如果M上面没有gap ， next-key locking的话，可以插入  ， 否则等待  (对其next-rec加insert intension lock，由于有gap锁，所以等待)

* insert 的流程(有唯一索引的情况)： insert N

1. 找到大于N的第一条记录M，以及前一条记录P
2. 如果M上面没有gap ， next-key locking的话，进入第三步骤  ， 否则等待(对其next-rec加insert intension lock，由于有gap锁，所以等待)
3. 检查p：
    判断p是否等于n：
         如果不等: 则完成插入（结束）
         如果相等：
                再判断P 是否有锁，
                    如果没有锁:
                        报1062错误（duplicate key） --说明该记录已经存在，报重复值错误
                        加S-lock  --说明该记录被标记为删除, 事务已经提交，还没来得及purge
                    如果有锁: 则加S-lock  --说明该记录被标记为删除，事务还未提交.

* insert intension lock 有什么用呢？锁的兼容矩阵是啥？

1. insert intension lock 是一种特殊的Gap lock，记住非常特殊哦
2. insert intension lock 和 insert intension lock 是兼容的，其次都是不兼容的
3. Gap lock 是为了防止insert， insert intension lock 是为了insert并发更快，两者是有区别的
4. 什么情况下会出发insert intension lock ？
    当insert的记录M的 next-record 加了Gap lock才会发生，record lock并不会触发

实战案例

RR 隔离级别
最后来一个比较复杂的案例作为结束
通过这几个案例，可以复习下之前讲过的理论，锁不仅对主键加，还要考虑二级索引哦

  • 环境
set tx_isolation = 'repeatable-read';

CREATE TABLE `a` (
   `a` int(11) NOT NULL,
   `b` int(11) DEFAULT NULL,
   `c` int(11) DEFAULT NULL,
   `d` int(11) DEFAULT NULL,
   PRIMARY KEY (`a`),
   UNIQUE KEY `idx_b` (`b`),
   KEY `idx_c` (`c`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 dba:lc_3> select * from a;
 +---+------+------+------+
 | a | b    | c    | d    |
 +---+------+------+------+
 | 1 |    3 |    5 |    7 |
 | 3 |    5 |    7 |    9 |
 | 5 |    7 |    9 |   11 |
 | 7 |    9 |   11 |   13 |
 +---+------+------+------+
 4 rows in set (0.00 sec)
  • 加锁语句
select * from a where c<9 for update;

锁结构：

TABLE LOCK table `lc_3`.`a` trx id 133601957 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601957 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601957 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d0110; asc    '   ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000007f66444; asc     dD;;
 2: len 7; hex fc0000271d011d; asc    '   ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

二级索引idx_c 加锁 next-key lock： (-∞,5],(5,7],(7,9]
primary key 加锁 record lock： [1]和[3]

  • 案例一 insert into a select 4,40,9,90

大家觉得能够插入成功吗？

dba:lc_3> insert into a select 4,40,9,90;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
...................waiting.................

显然是被锁住了

TABLE LOCK table `lc_3`.`a` trx id 133601961 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601961 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

  • 案例二 insert into a select 6,40,9,90;

大家觉得能够插入成功吗？


dba:lc_3> insert into a select 6,40,9,90;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

显然是插入成功了

时间： 2024-12-12 12:09:17

MySQL锁系列（七）之 锁算法详解的相关文章

Handler详解系列(七)——Activity.runOnUiThread()方法详解

MainActivity如下: package cc.testui3; import android.os.Bundle; import android.view.View; import android.view.View.OnClickListener; import android.widget.Button; import android.widget.TextView; import android.app.Activity; /** * Demo描述: * 在子线程中更改UI的方式三

MySQL数据备份之mysqldump的使用详解_Mysql

mysqldump常用于MySQL数据库逻辑备份. 1.各种用法说明 A. 最简单的用法: mysqldump -uroot -pPassword [database name] > [dump file] 上述命令将指定数据库备份到某dump文件(转储文件)中,比如: mysqldump -uroot -p123 test > test.dump 生成的test.dump文件中包含建表语句(生成数据库结构哦)和插入数据的insert语句. B. --opt 如果加上--opt参数则生成的du

深入浅出Mybatis系列（三）---配置详解之properties与environments（mybatis源码篇）

深入浅出Mybatis系列(三)---配置详解之properties与environments(mybatis源码篇) 上篇文章<深入浅出Mybatis系列(二)---配置简介(mybatis源码篇)>我们通过对mybatis源码的简单分析,可看出,在mybatis配置文件中,在configuration根节点下面,可配置properties.typeAliases.plugins.objectFactory.objectWrapperFactory.settings.environments

构建安全的Xml Web Service系列之wse之错误代码详解

原文:构建安全的Xml Web Service系列之wse之错误代码详解     WSE3.0现在还没有中文版的可以下载,使用英文版的过程中,难免会遇到各种各样的错误,而面对一堆毫无头绪的错误异常,常常会感到迷茫和无助,我起初就觉得这个框架使用起来真的很难用,有时候一些莫名其妙的错误会困扰我半天或者几天的时间,而一些英文的解释理解起来似是而非,很难一针见血的获得哪里出了问题,我将用几篇文章将wse所涉及到的错误代码和错误消息翻译成中文,并且根据个人的一点经验,提出引发错误的原因,并总结出解决建议

Javascript实现快速排序（Quicksort）的算法详解_javascript技巧

目前,最常见的排序算法大概有七八种,其中"快速排序"(Quicksort)使用得最广泛,速度也较快.它是图灵奖得主C. A. R. Hoare(1934--)于1960时提出来的. "快速排序"的思想很简单,整个排序过程只需要三步: (1)在数据集之中,选择一个元素作为"基准"(pivot). (2)所有小于"基准"的元素,都移到"基准"的左边:所有大于"基准"的元素,都移到"

Floyd求最短路径算法详解

倘若我们要在计算机上建立一个交通咨询系统则可以采用图的结构来表示实际的交通网络.其实现最基本的功能,求出任意两点间的最短路径, 求最短路径的经典方法有很多种,最常用的便是迪杰斯特拉算法和佛洛依德(Floyd)算法,这篇文章就着重介绍Floyd算法. 求两点之间的最短路径无外乎有两种情况,一种就是从一点直接到另一点,另一种就是从一点经过n个节点后再到另一个节点,比如说要从A到B,则有两种情况就是A直接到B,或者是从A经过N个节点后再到B,所以,我们假设Dis(AB)为节点A到节点B的最短路径的距离

深入浅出Mybatis系列（四）---配置详解之typeAliases别名（mybatis源码篇）

深入浅出Mybatis系列(四)---配置详解之typeAliases别名(mybatis源码篇) 上篇文章<深入浅出Mybatis系列(三)---配置详解之properties与environments(mybatis源码篇)> 介绍了properties与environments, 本篇继续讲剩下的配置节点之一:typeAliases. typeAliases节点主要用来设置别名,其实这是挺好用的一个功能, 通过配置别名,我们不用再指定完整的包名,并且还能取别名. 例如: 我们在使用 co

MySQL解决SQL注入的另类方法详解_Mysql

本文实例讲述了MySQL解决SQL注入的另类方法.分享给大家供大家参考,具体如下: 问题解读 我觉得,这个问题每年带来的成本可以高达数十亿美元了.本文就来谈谈,假定我们有如下 SQL 模板语句: select * from T where f1 = '{value1}' and f2 = {value2} 现在我们需要根据用户输入值填充该语句: value1=hello value2=5 我们得到了下面的 SQL 语句,我们再提交给数据库: select * from T where f1='h

Jquery揭秘系列:ajax原生js实现详解(推荐)_jquery

讲到ajax这个东西,我们要知道两个对象XMLHTTPRequest和ActiveXObject ,提供了对 HTTP 协议的完全的访问,包括做出 POST 和 HEAD 请求以及普通的 GET 请求的能力.可以同步或异步返回 Web 服务器的响应,并且能以文本或者一个 DOM 文档形式返回内容.XMLHTTPRequest基本上算是标准化了,兼容大部分浏览器ActiveXObject这玩儿意儿是微软的东西,所以是为了兼容IE版本,我们用的只是它的xmlHTTP功能. 为了功能的明确和清晰,我们

mysql之TIMESTAMP（时间戳）用法详解_Mysql

一.TIMESTAMP的变体 TIMESTAMP时间戳在创建的时候可以有多重不同的特性,如: 1.在创建新记录和修改现有记录的时候都对这个数据列刷新: TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 2.在创建新记录的时候把这个字段设置为当前时间,但以后修改时,不再刷新它: TIMESTAMP DEFAULT CURRENT_TIMESTAMP 3.在创建新记录的时候把这个字段设置为0,以后修改时刷新它: TIMES