什么是数据表的复合主键
 
所谓的复合主键 就是指你表的主键含有一个以上的字段组成
 
比如
 
create table test
 
(
 
name varchar(19),
 
id number,
 
value varchar(10),
 
primary key (name,id)
 
)
 
上面的name和id字段组合起来就是你test表的复合主键
 
它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性
 
一般情况下，主键的字段长度和字段数目要越少越好
 
KEY
 
1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
 
2. 如果Key是PRI,  那么该列是主键的组成部分
 
3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
 
4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL
 
如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI,又是UNI
 
那么"desc 表名"的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL
 
那么此时，显示PRI
 
一个唯一性索引列可以显示为PRI,并且该列不能含有空值，同时该表没有主键
 
一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引
 
因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是没一个单独的列依然可以有重复的值
 
只要ID+NAME是唯一的即可

 
背景
 
今天在配合其他项目组做系统压测，过程中出现了偶发的死锁问题。分析代码后发现有复合主键的update情况，更新复合主键表时只使用了一个字段更新，同时在事务内又有对该表的insert操作，结果出现了偶发的死锁问题。
 
比如表t_lock_test中有两个主键都为primary key(a,b)，但是更新时却通过update t_lock_test .. where a = ? ，然后该事务内又有insert into t_lock_test values(...)
 
InnoDB中的锁算法是Next-Key Locking，很可能是因为这个点导致的死锁，但是复合主键下会出发Next-Key Locking吗，那多列联合unique索引下又会触发Next-Key Locking吗，书上并没有找到答案，得实际测试一下。
 
InnoDB中的锁
 
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访[插图]。InnoDB存储引擎会在行级别上对表数据上锁，这固然不错。不过InnoDB存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。
 
由于使用锁时基本都是在InnoDB存储引擎下，所以跳过MyISAM，直接讨论InnoDB。
 
锁类型
 
InnoDB存储引擎实现了如下两种标准的行级锁：
 
共享锁(S Lock)，允许事务读一行数据
 
排它锁(x lOCK)，允许事务删除或更新一条数据
 
如果一个事务T1已经获得了r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变r的数据，成这种情况为锁兼容(Lock Compatible)。但若有其他的事务T3箱获得行r的排它锁，则比如等待T1、T2释放行r上的共享锁——这种情况称为锁不兼容。
 
排它锁和共享锁的兼容性：
 
\
 
X
 
S
 
X
 
不兼容
 
不兼容
 
S
 
不兼容
 
兼容
 
InnoDB中对数据进行Update操作会产生行锁，也可以显示的添加行锁(也就是平时所说的“悲观锁”)
 
select for update
 
锁算法
 
InnoDB有3种行锁的算法，其分别是：
 
Record Lock：单个行记录上的锁，就是字面意思的行锁
 
Record Lock会锁住索引记录(注意这里说的是索引，因为InnoDB下主键索引即数据),ruguo InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时对InnoDB存储引擎会使用隐士的主键来进行锁定。
 
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
 
Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身
 
Gap Lock和Next-Key Lock的锁定区间划分原则是一样的。
 
例如一个索引有10/11/13和20这四个值，那么该索引被划分的的区间为：
 
(-∞,10]
 
(10,11]
 
(11,13]
 
(13,20]
 
(20,+∞]
 
采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决Phantom Problem，这将在下一小节中介绍。而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁(predict lock)的一种改进。
 
当查询的索引含有唯一(unique)属性时(主键索引，唯一索引)InnoDB存储引擎会对Next-Key Lock优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
 
下面来看一个辅助索引(非唯一索引)下的锁示例：
 
CREATE TABLE z ( a INT, b INT, PRIMARY KEY(a), KEY(b) );
 
INSERT INTO z SELECT 1,1;
 
INSERT INTO z SELECT 3,1;
 
INSERT INTO z SELECT 5,3;
 
INSERT INTO z SELECT 7,6;
 
INSERT INTO z SELECT 10,8;
 
表z的列b是辅助索引，若果事务A中执行：
 
SELECT * FROM z WHERE b=3 FOR UPDATE
 
由于b列是辅助索引，所以此时会使用Next-Key Locking算法，锁定的范围是(1,3]。特别注意，InnoDB还会对辅助索引的下一个值加上Gap Lock，即还有一个辅助索引范围为(3,6]的锁。因此，若在新事务B中运行以下SQL，都会被阻塞：
 
1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;//S锁
 
2. INSERT INTO z SELECT 4,2;
 
3. INSERT INTO z SELECT 6,5;
 
第1个SQL不能执行，因为在事务A中执行的SQL已经对聚集索引中列a=5的值加上X锁，因此执行会被阻塞。
 
第2个SQL，主键插入4，没有问题，但是插入的辅助索引值2在锁定的范围(1,3]中，因此执行同样会被阻塞。
 
第3个SQL，插入的主键6没有被锁定，5也不在范围(1,3]之间。但插入的b列值5在另下一个Gap Lock范围(3,6]中，故同样需要等待。
 
而下面的SQL语句，由于不在Next-Key Lock和Gap Lock范围内，不会被阻塞，可以立即执行：
 
INSERT INTO z SELECT 8,6;
 
INSERT INTO z SELECT 2,0;
 
INSERT INTO z SELECT 6,7;
 
从上面的例子可以发现，Gap Lock的作用是为了组织多个事务将数据插入到统一范围内，这样会导致幻读问题(Phantom Problem)。例子中事务A已经锁定了b=3的记录。若此时没有Gap Lock锁定(3,6]，其他事务就可以插入索引b列为3的记录，这会导致事务A中的用户再次执行同样查询会返回不同的记录，即导致幻读问题的产生。
 
用户也可以通过以下两种方式来显示的关闭Gap Lock(但不推荐)：
 
将事务的隔离级别设置为READ COMMITED
 
将参数innodb_locks_unsafe_for_binlog设置为1
 
在InnoDB中，对于Insert的操作，会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许插入。对于上面的例子，事务A已经锁定了表z中b=3的记录，即已经锁定了(1,3]的范围，这时若在其他事务中执行如下插入也会导致阻塞：
 
INSERT INTO z SELECT 2,0
 
因为在辅助索引列b上插入值为2的记录时，会监测到下一个记录3已经被索引，修改b列值后，就可以执行了
 
INSERT INTO z SELECT 2,0
 
幻读(Phantom Problem)
 
幻读是指在同一事务下，连续执行两次同样的SQL语句可能会导致不同的结果，第二次的SQL可能会返回之前不存在的行。
 
在默认的事务隔离级别(REPEATABLE READ)下，InnoDB存储引擎采用Next—Key Locking机制来避免幻读问题。
 
复(联)合主键与锁
 
上面的锁机制介绍(摘自《Mysql技术内幕 InnoDB存储引擎 第2版》)，只是针对辅助索引和聚集索引，那么复合主键下行锁的表现形式又是怎么样呢？从书上并没有找到答案，实际来测试一下。
 
首先创建一个复合主键的表
 
CREATE TABLE `composite_primary_lock_test` (
 
`id1` int(255) NOT NULL,
 
`id2` int(255) NOT NULL,
 
PRIMARY KEY (`id1`,`id2`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (10, 10);
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 8);
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 6);
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 6);
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 3);
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 1);
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 1);
 
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (7, 1);
 
事务A先来查询id2=6的列，并添加行锁
 
select * from composite_primary_lock_test where id2 = 6 lock in share mode
 
此时的锁会降级到Record Lock吗？事务B Update一条Next-Key Lock范围内的数据(id1=1,id2=8)证明一下：
 
UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;
 
结果是UPDATE被阻塞了，那么再来试试加锁时在where中把两个主键都带上：
 
select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode
 
执行UPDATE
 
UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;
 
结果是UPDATE没有被阻塞
 
上面加锁的id2=6的数据，不只1条，那么再试试对唯一的数据id2=8，只根据一个主键加锁呢，会不会降级为行级锁：
 
select * from composite_primary_lock_test where id2 = 8 lock in share mode;
 
UPDATE `composite_primary_lock_test` SE WHERE `id1` = 12 AND `id2` = 10;
 
结果也是被阻塞了，实验证明：
 
复合主键下，如果加锁时不带上所有主键，InnoDB会使用Next-Key Locking算法，如果带上所有主键，才会当作唯一索引处理，降级为Record Lock，只锁当前记录。
 
多列索引(联合索引)与锁
 
上面只验证了复合主键下的锁机制，那么多列索引呢，会不会和复合索引机制相同？多列unique索引呢？
 
新建一个测试表，并初始化数据
 
CREATE TABLE `multiple_idx_lock_test` (
 
`id` int(255) NOT NULL,
 
`idx1` int(255) NOT NULL,
 
`idx2` int(255) DEFAULT NULL,
 
PRIMARY KEY (`id`,`idx1`) USING BTREE
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
ALTER TABLE `multiple_idx_lock_test`
 
ADD UNIQUE INDEX `idx_multi`(`idx1`, `idx2`) USING BTREE;
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (1, 1, 1);
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (5, 2, 2);
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (7, 3, 3);
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (4, 4, 4);
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (2, 4, 5);
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (3, 5, 5);
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (8, 6, 5);
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (6, 6, 6);
 
事务A查询增加S锁，查询时仅使用idx1列，并遵循最左原则：
 
select * from multiple_idx_lock_test where idx1 = 6 lock in share mode;
 
现在插入一条Next-Key Lock范围内的数据：
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);
 
结果是被阻塞了，再试一遍通过多列索引中所有字段来加锁：
 
select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode;
 
插入一条Next-Key Lock范围内的数据：
 
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);
 
结果是没有被阻塞
 
由此可见，当使用多列唯一索引时，加锁需要明确要锁定的行(即加锁时使用索引的所有列)，InnoDB才会认为该条记录为唯一值，锁才会降级为Record Lock。否则会使用Next-Key Lock算法，锁住范围内的数据。
 
总结
 
在使用Mysql中的锁时要谨慎使用，尤其时更新/删除数据时，尽量使用主键更新，如果在复合主键表下更新时，一定通过所有主键去更新，避免锁范围变大带来的死锁等问题。
 
好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对我们的支持。
 
参考
 
《Mysql技术内幕 InnoDB存储引擎 第2版》 - 姜承尧

什么是数据表的复合主键 

 
所谓的复合主键 就是指你表的主键含有一个以上的字段组成 

 
比如 

 
create table test 

 
( 

 
   name varchar(19), 

 
   id number, 

 
   value varchar(10), 

 
   primary key (name,id) 

 
) 

 

 
上面的name和id字段组合起来就是你test表的复合主键 

 
它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性 

 
一般情况下，主键的字段长度和字段数目要越少越好 
 
 
 KEY
 
 1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
 2. 如果Key是PRI,  那么该列是主键的组成部分
 3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
 4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL
 
 如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI,又是UNI
 那么"desc 表名"的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL
 那么此时，显示PRI
 
 一个唯一性索引列可以显示为PRI,并且该列不能含有空值，同时该表没有主键
 
 一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引
 因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是没一个单独的列依然可以有重复的值
 只要ID+NAME是唯一的即可
 

 
前为别名后为主查询getXXX语句中字段
 
          SELECT colid,colname FROM table1
 
SELECT * FROM table2 WHERE id = #{id} AND name = #{name}   
 
复合主键@IdClass
 
有时一个实体的主键可能同时为多个,例如同样是之前使用的“CustomerEO”实体,需要通过name和email来查找指定实体,当且仅当name和email的值完全相同时,才认为是相同的实体对象.要配 ...
 
MyBatis insert返回主键(sqlserver2008)
 
mybatis insert返回主键(sqlserver2008)   MyBatisXML配置,下面两种方式都行 方式1: 
 
MSSQL - 逻辑主键、业务主键和复合主键
 
转载自:http://blog.csdn.net/sunrise918/article/details/5575054 这几天对逻辑主键.业务主键和复合主键进行了一些思考,也在网上搜索了一下相关的讨论 ...
 
【Hibernate步步为营】--复合主键映射具体解释
 
上篇文章讨论了继承映射,它是对象模型中最主要的特性,对于继承映射它的主要区分是字段类型的不同,所以在生成表结构时须要有新列来标识数据的类型,能够使用标签并在标签中加入d ...
 
Mysql 多列形成主键(复合主键 )
 
什么是数据表的复合主键 所谓的复合主键 就是指你表的主键含有一个以上的字段组成 比如 create table test (    name varchar(19),    id number,    ...
 
Cassandra开发入门文档第二部分(timeuuid类型、复合主键、静态字段详解)
 
timeuuid类型 timeuuid具有唯一索引和日期时间的综合特性,可以与日期和时间函数联合使用,常用的关联函数: dateOf() now() minTimeuuid() and maxTime ...
 
Hibernate 表映射 主键生成策略与复合主键
 
主要分析三点: 一.数据表和Java类的映射 : 二.单一主键映射和主键的生成策略 : 三.复合主键的表映射 : 一.数据表和Java类的映射  Hibernate封装了数据库DDL语句,只需要将数据 ...
 
hibernate复合主键
 
需要用到实体类Role的主键和Menu的主键结合起来作为实体类RoleMenu的主键,那么通过Hibernate具体实现如下: RoleMenu实体类:(注意该实体类需要实现Serializable接 ...
 
Hibernate征途(七)之复合主键映射和集合映射
 
把这两种映射放到一起说,是因为这两种映射不像前面的复用型映射.数量和方向型映射那么分类鲜明,所以放到了这个“其他”里面. 复合主键映射 在关系模型中,复合主键和其他的主键方式没有很大区别,但是反映到对 ...
 
随机推荐
 
vue吃进去的object已经变了样,不在是原来的!
 
直接上代码: class data { public list:number[]; constructor() { this.list=[0,0,0]; } } class methods exten ...
 
spring mvc 多视图配置