MySQL数据写入的一些相关概念了解

    最近在使用Flink以二阶段提交的方式写入MySQL，其中涉及到了Flink的二阶段提交，MySQL的事务机制，锁机制等等。本文来介绍下MySQL事务性写入时的一些概念，给自己留个印象。至于如何使用Flink的二阶段提交写入MySQL，后续会另外开一篇文章进行说明。

 

事务性写入MySQL Demo：

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
       
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection("jdbc:mysql://***IP***:3306/test?characterEncoding=UTF-8", "mysql", "mysql"); 
        int batchSize = 1000;
        // 设置自动提交为false
        conn.setAutoCommit(false);
        ps = conn.prepareStatement("insert into test (id) values (?)");
        for (long i = 1; i < count; i++) {
            ps.setLong(1, i);
            ps.addBatch();/
            if (i % batchSize == 0) {
                ps.executeBatch();
            }
        }
        // 提交事务
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        ps.close();
        conn.close();
    }
}

    但是真正执行的时候发现这样写入MySQL的速度其实并不快！一秒大概只能写2000条左右的数据...MySQL的性能有这么差么？！ 

    后来翻来翻去查了下资料，发现这里的batch是个假的batch，实际并没有真正的实现批量写入。需要在jdbc连接的url后面再加上 rewriteBatchedStatements=true 参数，MySQL服务端才会真正实现批量写入。测试了下，写入性能提高了4-5倍！

 

MySQL的事务机制介绍：

    事务用来维护数据库的完整性，它可以保证成批的MySQL操作要么完全执行，要么完全不执行。事务具有ACID四个特性，即：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

    可以看到，数据库在隔离性方面有大作文章，设置了四种不同的隔离等级，限制多个事务同时提交时的行为，隔离等级越高，数据库的并发能力就越弱，但是越不容易会出问题。当多个客户端线程同时执行事务操作时，可能出现三种问题：

    脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

    不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

    幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

隔离等级	脏读	不可重复读	幻读
READ UNCOMMITTED（读未提交）	可能	可能	可能
READ COMMITTED（读已提交）Oracle默认	避免	可能	可能
REPEATABLE READ（可重复读）MySQL默认	避免	避免	可能
SERIALIZABLE（可序列化）	避免	避免	避免

    但是互联网中MySQL隔离性等级应当选“读已提交”。参考网页中有详细的说明，MySQL之所以默认选择可重复读是因为比较早的版本的一个Bug造成的，现在已经不存在这个问题了！

    MySQL内部有锁机制来保证事务并发的正确性，这个锁机制也太多太复杂了...先在参考里面留个记录，后面有时间再仔细研究研究吧...当前还是想先把常用的大数据组件的一些机制了解下，为了调优

 

MySQL的事务 VS Redis的事务：

    当时方案调研的时候，对MySQL和Redis都进行了调研，看到Redis也支持事务，所以此处把Redis的事务顺便也说一下。

    为了保持简单，redis事务保证了其中的一致性和隔离性，不满足原子性和持久性。与关系式数据库中的事务执行失败回滚不同，Redis事务中有某条/某些命令执行失败了，事务中其他命令仍然会继续执行，Redis无法进行回滚，这违反了原子性。而且Redis的事务不过是用队列包裹起了一组 Redis 命令，并没有提供任何额外的持久性功能，所以Redis事务的持久性由 Redis 所使用的持久化模式决定。

    Redis事务为什么要这么设计呢？！那是因为Redis命令有语法错误时，命令才会执行失败，而Redis事务所有命令在放入事务队列期间，Redis能够发现此类问题，或者对某个键执行不符合其数据类型的操作，所以意味着只有程序错误才会导致Redis事务执行失败，这种错误很有可能在程序开发期间发现，一般很少在生产环境发现。为了简单和快速，所以Redis设计成没有回滚。

 

 

参考：

    https://segmentfault.com/q/1010000013789017（MySQL使用多线程批量插入性能更优）

    https://www.jianshu.com/p/04d3d235cb9f（MySQL批量写入源码解析）

    https://blog.csdn.net/weixin_40255793/article/details/79735665（MySQL事务的隔离性）

    https://zhuanlan.zhihu.com/p/59061106 （互联网项目中MySQL隔离性应该选择 读已提交(Read Commited)）

    https://learnku.com/articles/39212?order_by=vote_count& （MySQL中的锁机制介绍）

    https://zhuanlan.zhihu.com/p/357315436（Redis事务介绍）

    https://www.webyang.net/Html/web/article_411.html（Redis事务介绍）

    https://www.v2ex.com/t/505341（唯一索引写入时可能会遇到死锁问题）

    https://www.iteye.com/blog/825635381-2339434（MySQL并发插入，出现duplicate时，会默认加S锁，导致死锁）