mysql innodb 并发 插入 慢_MySQL/InnoDB的并发插入Concurrent Insert

MySQL/InnoDB的并发插入Concurrent Insert

表people建表语句：ENGINE=InnoDB

CREATE TABLE people  (

person_id BIGINT NOT NULL AUTO_INCREMENT,

first_name VARCHAR(20),

last_name VARCHAR(20),

PRIMARY KEY (person_id)

);

两个会话：会话一和会话二

在会话一中，执行如下sql语句：

mysql> use local_database;

Database changed

mysql> select @@autocommit;

+--------------+

| @@autocommit |

+--------------+

|            1 |

+--------------+

1 row in set (0.00 sec)

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;

+--------------+

| @@autocommit |

+--------------+

|            0 |

+--------------+

1 row in set (0.00 sec)

mysql> select * from people;

+-----------+------------+-----------+

| person_id | first_name | last_name |

+-----------+------------+-----------+

|         1 | 1111       | 1111      |

+-----------+------------+-----------+

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into people (first_name,last_name) values ('1111','1111');

Query OK, 1 row affected (0.12 sec)

mysql> select * from people;

+-----------+------------+-----------+

| person_id | first_name | last_name |

+-----------+------------+-----------+

|         1 | 1111       | 1111      |

|         3 | 1111       | 1111      |

+-----------+------------+-----------+

2 rows in set (0.00 sec)

上面这些sql 语句最终的操作就是手动开启了一个事务，然后提交了一个insert语句，注意没有手动提交事务。。

此时在另一个会话二中进行如下操作

mysql> use local_database;

Database changed

mysql> select @@autocommit;

+--------------+

| @@autocommit |

+--------------+

|            1 |

+--------------+

1 row in set (0.00 sec)

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;

+--------------+

| @@autocommit |

+--------------+

|            0 |

+--------------+

1 row in set (0.00 sec)

mysql> select * from people;

+-----------+------------+-----------+

| person_id | first_name | last_name |

+-----------+------------+-----------+

|         1 | 1111       | 1111      |

+-----------+------------+-----------+

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into people (first_name,last_name) values ('2222','2222');

Query OK, 1 row affected (0.00 sec)

mysql> select * from people;

+-----------+------------+-----------+

| person_id | first_name | last_name |

+-----------+------------+-----------+

|         1 | 1111       | 1111      |

|         4 | 2222       | 2222      |

+-----------+------------+-----------+

2 rows in set (0.00 sec)

上面的sql语句最终的操作是手动开启了一个事务，执行了insert语句，而没有提交事务，然后select查看只会看到当前会话的操作结果，而没有会话一的操作结果。这就是mysql默认事务隔离级别——可重复读。

做到这里，我验证的不是可重复读的事务隔离级别，我其实想验证的是会话在事务内执行insert语句会不会给表加锁(会给表加锁，AUTO-INC lock)，通过结果显示，可知，不同的会话在事务内执行insert语句，而不会阻塞其他会话事务内的insert语句。

但最终我产生了疑问，MySQL/InnoDB是如何处理并发插入的。

MySQL/InnoDB的并发插入

说起auto_increment的并发插入，就要理解auto_increment的机制了。见文章：

下面我写了两个程序测试并发插入，一个是单线程的写入10w条数据，一个是100条线程单个线程写入1000条数据。可以运行一下程序做一个对比。

建表语句

CREATE TABLE people_thread  (

person_id BIGINT NOT NULL AUTO_INCREMENT,

first_name VARCHAR(20),

last_name VARCHAR(20),

thread_name VARCHAR(20),

PRIMARY KEY (person_id)

);

总之还是多线程的效率比较高：测试数据如下

单线程的运行时间为：-- 16656ms

多线程的最长的运行时间为：-- 12632ms

JDBCTest2.java

package com.lyx.other;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class JDBCTest2 {

public static void main(String args[]) throws SQLException {

long startTime = System.currentTimeMillis(); // 获取开始时间

Connection conn = null;

PreparedStatement ps = null;

String sql = "insert into people_thread (first_name ,last_name ,"

+ "thread_name) values (?,?,?)";

try {

conn = getConnection();

conn.setAutoCommit(false);

ps = conn.prepareStatement(sql);

for (int i = 0; i 

ps.setString(1, Integer.toString(i));

ps.setString(2, Integer.toString(i));

ps.setString(3, Integer.toString(i));

ps.addBatch();

// 一批提交一次

if (i % 10 == 0) {

ps.executeBatch();

ps.clearBatch();

}

}

ps.executeBatch();

conn.commit();

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

} finally {

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

long endTime = System.currentTimeMillis(); // 获取结束时间

System.out.println("程序运行时间： " + (endTime - startTime) + "ms");

}

/* 获取数据库连接的函数 */

public static Connection getConnection() {

Connection con = null; // 创建用于连接数据库的Connection对象

try {

Class.forName("com.mysql.jdbc.Driver");// 加载Mysql数据驱动

con = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/local_database", "root",

"034039");// 创建数据连接

} catch (Exception e) {

System.out.println("数据库连接失败" + e.getMessage());

}

return con; // 返回所建立的数据库连接

}

}

JDBCTest3.java

package com.lyx.other;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class JDBCTest3 {

public static void main(String[] args) {

// 创建10个线程执行插入操作

for (int i = 0; i 

final int n = i;

Runnable task = new Runnable() {

public void run() {

Thread.currentThread().setName("thread_" + n);

insertByBatch(Thread.currentThread().getName());

}

};

Thread thread = new Thread(task);

thread.start();

}

}

public static void insertByBatch(String name) {

long startTime = System.currentTimeMillis(); // 获取开始时间

Connection conn = null;

PreparedStatement ps = null;

String sql = "insert into people_thread (first_name ,last_name ,"

+ "thread_name) values (?,?,?)";

try {

conn = getConnection();

conn.setAutoCommit(false);

ps = conn.prepareStatement(sql);

for (int i = 0; i 

ps.setString(1, Integer.toString(i));

ps.setString(2, Integer.toString(i));

ps.setString(3, name);

ps.addBatch();

// 一批提交一次

if (i % 10 == 0) {

ps.executeBatch();

ps.clearBatch();

}

}

ps.executeBatch();

conn.commit();

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

} finally {

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

long endTime = System.currentTimeMillis(); // 获取结束时间

System.out.println("程序运行时间： " + (endTime - startTime) + "ms");

}

/* 获取数据库连接的函数 */

public static Connection getConnection() {

Connection con = null; // 创建用于连接数据库的Connection对象

try {

Class.forName("com.mysql.jdbc.Driver");// 加载Mysql数据驱动

con = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/local_database", "root",

"034039");// 创建数据连接

} catch (Exception e) {

System.out.println("数据库连接失败" + e.getMessage());

}

return con; // 返回所建立的数据库连接

}

}

============END============