mysql DUPLICATE KEY UPDATE 问题

DUPLICATE KEY UPDATE batch执行时出死锁错误

背景知识

一、 mysql  insert 与 duplicate key:

典型的插入语句:

多条:INSERT INTO tablename (columnA, columnB, columnC)

VALUES ('a', 1, 2), ('b', 7, 5)

单条:INSERT INTO tablename SET columnA='a', columnB=1, columnC=2

复制:INSERT [options1] [INTO] tablename [ (columnlist) ] SELECT …

若表已设置主键如columnA,重复的插入无效

ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

如果数据库中已有某条数据,以下的两条语句可等同:

INSERT INTO tablename (id, data) VALUES (1, 10)
ON DUPLICATE KEY UPDATE data=data+10;
UPDATE tablename SET data=data+10 WHERE id=1;

duplicate key语句一般应用在 格式化多条更新语句:

INSERT INTO tablename (id, data) VALUES (1, 10), (2, 15) ON DUPLICATE KEY UPDATE data=data+VALUE(data)

二、innodb表提高插入效率

查询表使用的引擎:  show create table tablename;

innodb 的存储引擎提供行级锁,支持共享锁和排他锁两种锁定模式,以及四种不同的隔离级别。

对于Innodb 类型的表,我们有以下几种方式可以提高导入的效率: a. 因为Innodb 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺 序排列,可以有效的提高导入数据的效率。如果Innodb 表没有主键,那么系统会默认 创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这个优势提高 导入数据的效率。 b. 在导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。 c. 如果应用使用自动提交的方式,建议在导入前执行SET AUTOCOMMIT=0,关闭自动 提交,导入结束后再执行SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。

如果如果你同时从同一客户插入很多行,使用多个值表的INSERT 语句。这比使用分开INSERT 语句快(在一些情况中几倍)。

你从不同客户插入很多行,能通过使用INSERT DELAYED 语句得到更高的速度。Delayed 的含义是让insert 语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘;这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,在所有其他用户对表的读写完后才进行插入;

在项目中遇到的问题时,使用了这种insert处理,但是分库分表,数据表类型为innodb,  tablename各不相同,duplicate key只是用于合并update和insert语句。

 "java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction" 错误状况为 第一个插入即出错,或一个batch中重复一个key,插入多个值。 貌似mysql有这个bug(http://bugs.mysql.com/bug.php?id=52020

使用threadlocal去获取操作数据库的对象,static对象,获取pool的连接并执行批处理方法

PRIMARY KEY

UNIQUE KEY 的区别

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏日常学python

情人节福利,手把手教你学会mysql语句操作

这是我的第八篇原创文章 上篇文章简单说了下数据库,并教了怎样安装mysql工具和可视化工具,不知道你现在安装好了没?那今天我们继续说mysql,今天说下mysq...

37714
来自专栏我是攻城师

如何在shell下使用JavaScript和Mongodb交互?

3965
来自专栏码云1024

sql 复习练习

4266
来自专栏IT技术精选文摘

MySQL-性能优化-索引和查询优化

1121
来自专栏琯琯博客

MySQL 查询优化

21111
来自专栏程序你好

30个MySQL千万级大数据SQL查询优化技巧详解

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

3923
来自专栏用户2442861的专栏

关于sql中索引的优缺点(面试常考)

   原文       http://blog.csdn.net/dinglang_2009/article/details/5951428

1531
来自专栏沃趣科技

内存分配统计视图 | 全方位认识 sys 系统库

在上一篇《按 file 分组统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中按 file 分组统计的视图,本期的内容将为大家介绍内存事件...

2250
来自专栏精讲JAVA

百万数据查询优化技巧三十则

对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 应尽量避免在 where 子句中对字段进行 null ...

2029
来自专栏杨建荣的学习笔记

MySQL创建表失败的问题

今天有一个朋友问我一个MySQL的建表问题,问题的现象是创建表失败,根据他的反馈,问题比较奇怪, CREATE TABLE XXX ..此处省略260多个字...

3787

扫码关注云+社区