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 changedmysql> 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 changedmysql> 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 < 100000; 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 < 100; 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 < 1000; 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============