mysql并发安全问题的思考(避免并发插入时出现相同的用户名之unique 索引的使用)

最新推荐文章于 2024-07-22 03:56:02 发布

.net core game

最新推荐文章于 2024-07-22 03:56:02 发布

阅读量625

点赞数

分类专栏： # mysql

本文链接：https://blog.csdn.net/themagickeyjianan/article/details/106043265

版权

mysql 专栏收录该内容

32 篇文章 2 订阅

订阅专栏

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo02_Insert {

    public void f() {
        Statement stmt = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            for (int i = 0; i < 10; i++) {
                String name = "嗷嗷啊";
                String sql = "insert into account values(null, '" + name + i + "', 3000)";
                System.out.println(sql);
                conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "jianan");
                stmt = conn.createStatement();
                int count = stmt.executeUpdate(sql);
                System.out.println(count);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) {
        new Thread(() -> {
            new JdbcDemo02_Insert().f();
        }).start();

        new Thread(() -> {
            new JdbcDemo02_Insert().f();
        }).start();

        new Thread(() -> {
            new JdbcDemo02_Insert().f();
        }).start();

        new Thread(() -> {
            new JdbcDemo02_Insert().f();
        }).start();

        new Thread(() -> {
            new JdbcDemo02_Insert().f();
        }).start();

        new Thread(() -> {
            new JdbcDemo02_Insert().f();
        }).start();

        new Thread(() -> {
            new JdbcDemo02_Insert().f();
        }).start();
    }
}

输出

insert into account values(null, '嗷嗷啊0', 3000)
insert into account values(null, '嗷嗷啊0', 3000)
insert into account values(null, '嗷嗷啊0', 3000)
insert into account values(null, '嗷嗷啊0', 3000)
insert into account values(null, '嗷嗷啊0', 3000)
insert into account values(null, '嗷嗷啊0', 3000)
insert into account values(null, '嗷嗷啊0', 3000)
1
insert into account values(null, '嗷嗷啊1', 3000)
1
insert into account values(null, '嗷嗷啊2', 3000)
1
insert into account values(null, '嗷嗷啊3', 3000)
1
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '嗷嗷啊0' for key 'name'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
	at cn.itcast.jdbc.JdbcDemo02_Insert.f(JdbcDemo02_Insert.java:21)
	at cn.itcast.jdbc.JdbcDemo02_Insert.lambda$main$6(JdbcDemo02_Insert.java:74)
	at java.lang.Thread.run(Thread.java:748)
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '嗷嗷啊0' for key 'name'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
	at cn.itcast.jdbc.JdbcDemo02_Insert.f(JdbcDemo02_Insert.java:21)
	at cn.itcast.jdbc.JdbcDemo02_Insert.lambda$main$1(JdbcDemo02_Insert.java:54)
	at java.lang.Thread.run(Thread.java:748)
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '嗷嗷啊0' for key 'name'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
	at cn.itcast.jdbc.JdbcDemo02_Insert.f(JdbcDemo02_Insert.java:21)
	at cn.itcast.jdbc.JdbcDemo02_Insert.lambda$main$4(JdbcDemo02_Insert.java:66)
	at java.lang.Thread.run(Thread.java:748)
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '嗷嗷啊0' for key 'name'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
	at cn.itcast.jdbc.JdbcDemo02_Insert.f(JdbcDemo02_Insert.java:21)
	at cn.itcast.jdbc.JdbcDemo02_Insert.lambda$main$3(JdbcDemo02_Insert.java:62)
	at java.lang.Thread.run(Thread.java:748)
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '嗷嗷啊0' for key 'name'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
	at cn.itcast.jdbc.JdbcDemo02_Insert.f(JdbcDemo02_Insert.java:21)
	at cn.itcast.jdbc.JdbcDemo02_Insert.lambda$main$2(JdbcDemo02_Insert.java:58)
	at java.lang.Thread.run(Thread.java:748)
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '嗷嗷啊0' for key 'name'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
	at cn.itcast.jdbc.JdbcDemo02_Insert.f(JdbcDemo02_Insert.java:21)
	at cn.itcast.jdbc.JdbcDemo02_Insert.lambda$main$5(JdbcDemo02_Insert.java:70)
	at java.lang.Thread.run(Thread.java:748)
insert into account values(null, '嗷嗷啊4', 3000)
1
insert into account values(null, '嗷嗷啊5', 3000)
1
insert into account values(null, '嗷嗷啊6', 3000)
1
insert into account values(null, '嗷嗷啊7', 3000)
1
insert into account values(null, '嗷嗷啊8', 3000)
1
insert into account values(null, '嗷嗷啊9', 3000)
1

实际插入结果: 可见并发插入的情况下，由于mysql设置了name为唯一，最终是正确的。

unique索引如下

总结：可见jdbc还是比较给力的。自己简单封装下即可，游戏业务中不需要用复杂的mybatis，造成框架的复杂性减少黑盒！

简单的代码就实现：

1.事务

2.数据库CURD基本操作

.net core game

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql并发安全问题的思考(避免并发插入时出现相同的用户名之unique 索引的使用)

package cn.itcast.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class JdbcDemo02_Insert { public void f() { Statement stmt = null; Connection conn = .
复制链接

扫一扫

专栏目录