数据库事务实验 (ISOLATION_DEFAULT)

2016/3/18 posted in  数据库

ISOLATION_DEFAULT: Use the default isolation level of the underlying datastore.

The default isolation level in MySQL’s InnoDB is REPEATABLE READ.

实验一

  • @transaction标签在class上
  • mapper xml没有配置flushCache以及useCache
  • 初始表数据为空

先执行insert20次，每次休眠1秒

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

再执行select30次，每次休眠1秒

    List<WechatUser> wechatUser = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUser.size());

结果：

2016-03-18 12:49:39 [org.springframework.transaction.annotation.AnnotationTransactionAttributeSource]-[DEBUG] Adding transactional method 'WechatRegistServiceImpl.insertUser' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''

2016-03-18 12:49:39 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Creating new transaction with name [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl.insertUser]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''

2016-03-18 12:49:40 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Acquired Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] for JDBC transaction

2016-03-18 12:49:40 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Switching JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] to manual commit

2016-03-18 12:49:40 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Creating a new SqlSession

2016-03-18 12:49:40 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353]

2016-03-18 12:49:40 [org.mybatis.spring.transaction.SpringManagedTransaction]-[DEBUG] JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] will be managed by Spring

2016-03-18 12:49:40 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())

2016-03-18 12:49:40 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Parameters: 1234567890(String), insert(String), null

2016-03-18 12:49:40 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] <== Updates: 1

2016-03-18 12:49:40 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353]

2016-03-18 12:49:40 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 0 条

2016-03-18 12:49:41 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353] from current transaction

2016-03-18 12:49:41 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())

2016-03-18 12:49:41 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Parameters: 1234567890(String), insert(String), null

2016-03-18 12:49:41 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] <== Updates: 1

2016-03-18 12:49:41 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353]
2016-03-18 12:49:41 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 1 条

...

2016-03-18 12:49:43 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Looking up handler method for path /select.html

2016-03-18 12:49:43 [org.springframework.transaction.annotation.AnnotationTransactionAttributeSource]-[DEBUG] Adding transactional method 'WechatRegistServiceImpl.selectUser' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''

2016-03-18 12:49:43 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Creating new transaction with name [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl.selectUser]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''

2016-03-18 12:49:43 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Acquired Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] for JDBC transaction

2016-03-18 12:49:43 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Switching JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] to manual commit

2016-03-18 12:49:43 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Creating a new SqlSession

2016-03-18 12:49:43 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d]

2016-03-18 12:49:43 [org.mybatis.spring.transaction.SpringManagedTransaction]-[DEBUG] JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] will be managed by Spring

2016-03-18 12:49:43 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER

2016-03-18 12:49:43 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Parameters:

2016-03-18 12:49:43 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] <== Total: 0

2016-03-18 12:49:43 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d]

2016-03-18 12:49:43 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：0 用户数量：0

2016-03-18 12:49:44 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353] from current transaction

2016-03-18 12:49:44 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())

...

2016-03-18 12:49:44 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d] from current transaction

2016-03-18 12:49:44 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d]

2016-03-18 12:49:44 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：1 用户数量：0

...

2016-03-18 12:50:00 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353]

2016-03-18 12:50:00 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353]

2016-03-18 12:50:00 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21e3b353]

2016-03-18 12:50:00 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Initiating transaction commit

2016-03-18 12:50:00 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Committing JDBC transaction on Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver]

2016-03-18 12:50:00 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Releasing JDBC Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] after transaction

2016-03-18 12:50:00 [org.springframework.jdbc.datasource.DataSourceUtils]-[DEBUG] Returning JDBC Connection to DataSource

2016-03-18 12:50:11 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d] from current transaction

2016-03-18 12:50:11 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d]

2016-03-18 12:50:11 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：28 用户数量：0

2016-03-18 12:50:12 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d] from current transaction

2016-03-18 12:50:12 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a50701d]

2016-03-18 12:50:12 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：29 用户数量：0

再发一次select请求

...

2016-03-18 12:59:34 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER
2016-03-18 12:59:34 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Parameters:
2016-03-18 12:59:34 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] == Total: 20
2016-03-18 12:59:34 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75ab43b7]
2016-03-18 12:59:34 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：0 用户数量：20
2016-03-18 12:59:35 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75ab43b7] from current transaction
2016-03-18 12:59:35 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75ab43b7]
2016-03-18 12:59:35 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：1 用户数量：20
2016-03-18 12:59:36 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75ab43b7] from current transaction
2016-03-18 12:59:36 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75ab43b7]
2016-03-18 12:59:36 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：2 用户数量：20

结论

  • 读在一个事务中读的都是缓存，一个事务进行中没有新的请求过去
  • 事务的状态是：PROPAGATION_REQUIRED,ISOLATION_DEFAULT
  • 先是Creating a new SqlSession，然后Releasing transactional SqlSession，再有请求则是Fetched SqlSession from current transaction
  • 读事务中第一次读，拿到的数据是写事务进行之前数据库的状态；之后拿到的数据都是在缓存中拿到的，无论写事务状态是什么。

实验二

  • @transaction标签在class上
  • mapper xml没有配置flushCache以及useCache

先执行insert20次，每次休眠1秒（同一个insert方法）

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

再执行insert20次，每次休眠1秒（同一个insert方法）

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

结果：

2016-03-18 13:16:56 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())
2016-03-18 13:16:56 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Parameters: 1234567890(String), insert(String), null
2016-03-18 13:16:56 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] == Updates: 1
2016-03-18 13:16:56 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3cb57f97]
2016-03-18 13:16:56 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 1 条
2016-03-18 13:16:57 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@302693e9] from current transaction
2016-03-18 13:16:57 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())
2016-03-18 13:16:57 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Parameters: 1234567890(String), insert(String), null
2016-03-18 13:16:57 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] == Updates: 1
2016-03-18 13:16:57 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@302693e9]
2016-03-18 13:16:57 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 8 条

结论

  • 两次写事务并行进行

实验三

  • @transaction标签在方法上
  • mapper xml没有配置flushCache以及useCache
  • 初始表数据为空

先执行insert20次，每次休眠1秒

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

再执行select30次，每次休眠1秒

    List<WechatUser> wechatUser = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUser.size());

结果：

同实验一

实验四

  • @transaction标签在方法上
  • mapper xml没有配置flushCache以及useCache

先执行insert20次，每次休眠1秒

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

再执行insert20次，每次休眠1秒

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

结果：

同实验二

实验五

  • @transaction标签在方法上
  • mapper xml设置flushCache="true" useCache="false"
    <select id="select" resultMap="BaseResultMap" parameterType="java.lang.String" flushCache="true" useCache="false">
  • 初始表数据为空

先执行insert20次，每次休眠1秒

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

再执行select30次，每次休眠1秒

    List<WechatUser> wechatUser = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUser.size());

结果：

2016-03-18 13:49:37 [org.springframework.transaction.annotation.AnnotationTransactionAttributeSource]-[DEBUG] Adding transactional method 'WechatRegistServiceImpl.insertUser' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
2016-03-18 13:49:37 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Creating new transaction with name [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl.insertUser]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
2016-03-18 13:49:38 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Acquired Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver] for JDBC transaction

...

2016-03-18 13:49:38 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Preparing: insert into WECHAT_USER (open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date) values (?, ?, 1, ?, "system", now(), "system", now())

2016-03-18 13:49:38 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] ==> Parameters: 1234567890(String), insert(String), null

2016-03-18 13:49:38 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.insert]-[DEBUG] <== Updates: 1

2016-03-18 13:49:38 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2e6ab308]

2016-03-18 13:49:38 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 0 条

...

2016-03-18 13:49:39 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 1 条

...

2016-03-18 13:49:40 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 2 条

2016-03-18 13:49:40 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Looking up handler method for path /select.html

2016-03-18 13:49:40 [org.springframework.transaction.annotation.AnnotationTransactionAttributeSource]-[DEBUG] Adding transactional method 'WechatRegistServiceImpl.selectUser' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''

...

2016-03-18 13:49:40 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER

...

2016-03-18 13:49:40 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：0 用户数量：0

...

2016-03-18 13:49:41 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER

...

2016-03-18 13:49:41 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：1 用户数量：0

...

2016-03-18 13:50:08 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER

2016-03-18 13:50:08 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：28 用户数量：0

2016-03-18 13:50:09 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.select]-[DEBUG] ==> Preparing: select user_id, open_id, user_name, use_yn, authority_value, insert_id, insert_date, modify_id, modify_date from WECHAT_USER

2016-03-18 13:50:09 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：29 用户数量：0

结论

  • 读事务中第一次读，拿到的数据是写事务进行之前数据库的状态。

实验六

  • @transaction标签在方法上

先执行update20次，每次休眠1秒（update同一条数据）

    int result = wechatUserMapper.update(wechatUser);
    logger.info("----------------修改数据：" + i + "  结果" + result);

再执行update20次，每次休眠1秒（update同一条数据）

    int result = wechatUserMapper.update(wechatUser);
    logger.info("----------------修改数据：" + i + "  结果" + result);

结果：

2016-03-18 14:50:30 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.update]-[DEBUG] ==> Preparing: update WECHAT_USER SET open_id = ?, user_name = ?, modify_date = now() where open_id = "abc"

2016-03-18 14:50:30 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------修改数据：0 结果1

2016-03-18 14:50:31 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77e2cc94] from current transaction

2016-03-18 14:50:31 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.update]-[DEBUG] ==> Preparing: update WECHAT_USER SET open_id = ?, user_name = ?, modify_date = now() where open_id = "abc"

2016-03-18 14:50:31 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------修改数据：1 结果0

2016-03-18 14:50:32 [org.springframework.web.servlet.DispatcherServlet]-[DEBUG] DispatcherServlet with name 'spring' processing GET request for [/springtest/update.html]

2016-03-18 14:50:32 [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping]-[DEBUG] Looking up handler method for path /update.html

...

2016-03-18 14:50:32 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.update]-[DEBUG] ==> Preparing: update WECHAT_USER SET open_id = ?, user_name = ?, modify_date = now() where open_id = "abc"

2016-03-18 14:50:32 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------修改数据：2 结果0

...

2016-03-18 14:50:49 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------修改数据：19 结果0

2016-03-18 14:50:50 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77e2cc94]

2016-03-18 14:50:50 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77e2cc94]

2016-03-18 14:50:50 [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77e2cc94]

2016-03-18 14:50:50 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Initiating transaction commit

2016-03-18 14:50:50 [org.springframework.jdbc.datasource.DataSourceTransactionManager]-[DEBUG] Committing JDBC transaction on Connection [jdbc:mysql://localhost:3306/sip?useUnicode=yes&characterEncoding=UTF8, UserName=root@localhost, MySQL-AB JDBC Driver]

...

2016-03-18 14:50:50 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------修改数据：0 结果0

...

2016-03-18 14:50:51 [cn.hao24.mobauto.mapper.wechatusers.WechatUserMapper.update]-[DEBUG] ==> Preparing: update WECHAT_USER SET open_id = ?, user_name = ?, modify_date = now() where open_id = "abc"

2016-03-18 14:50:51 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------修改数据：1 结果0

结论

  • 当两个写事务都是操作同一个数据的时候，第二个写事务会等第一个写事务提交之后才会进行。

实验七

  • @transaction标签在方法上

先执行update20次，每次休眠1秒（update不同一条数据）

    int result = wechatUserMapper.update(wechatUser);
    logger.info("----------------修改数据：" + i + "  结果" + result);

再执行update20次，每次休眠1秒（update不同一条数据）

    int result = wechatUserMapper.update(wechatUser);
    logger.info("----------------修改数据：" + i + "  结果" + result);

结果：

同实验六

实验八

  • @transaction标签在方法上

先执行update20次，每次休眠1秒

    int result = wechatUserMapper.update(wechatUser);
    logger.info("----------------修改数据：" + i + "  结果" + result);

再执行insert20次，每次休眠1秒

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

结果：

先执行update，update事务结束后，执行insert

实验九

  • @transaction标签在class上

先执行insert20次，每次休眠1秒（不同insert方法）

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

再执行insert20次，每次休眠1秒（不同insert方法）

    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

结果：

并行

实验十

  • @transaction标签在方法上
  • mapper xml设置flushCache="true" useCache="false"
    <select id="select" resultMap="BaseResultMap" parameterType="java.lang.String" flushCache="true" useCache="false">

先执行select + insert20次，每次休眠1秒（同一个select + insert方法）

    List<WechatUser> wechatUserList = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUserList.size());
    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

再执行select + insert20次，每次休眠1秒（同一个select + insert方法）

    List<WechatUser> wechatUserList = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUserList.size());
    wechatUserMapper.insert(wechatUser);
    logger.info("----------------写入数据第   " + i + " 条");

结果：

2016-03-18 16:17:43 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：5 用户数量：5

...

2016-03-18 16:17:43 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 5 条

...

2016-03-18 16:17:44 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：0 用户数量：0

...

2016-03-18 16:17:44 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 0 条

...

2016-03-18 16:17:44 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：6 用户数量：6

...

2016-03-18 16:17:44 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 6 条

...

2016-03-18 16:17:45 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：1 用户数量：1

...

2016-03-18 16:17:58 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：19 用户数量：19

...

2016-03-18 16:17:58 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 19 条

...

2016-03-18 16:17:58 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------读出数据：14 用户数量：14

...

2016-03-18 16:17:58 [cn.hao24.mobauto.db.service.wechatuser.impl.WechatRegistServiceImpl]-[INFO] ----------------写入数据第 14 条

结论

  • 两个读写事务或并行运行；但是他们对数据库的影响不会作用到彼此，即大家读的都是快照

实验十一

  • @transaction标签在方法上
  • mapper xml设置flushCache="true" useCache="false"
    <select id="select" resultMap="BaseResultMap" parameterType="java.lang.String" flushCache="true" useCache="false">

先执行select + update20次，每次休眠1秒（同一个select + update方法）

    List<WechatUser> wechatUserList = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUserList.size());
    int result = wechatUserMapper.update(wechatUser);
    logger.info("----------------修改数据：" + i + "  结果" + result);

再执行select + update20次，每次休眠1秒（同一个select + update方法）

    List<WechatUser> wechatUserList = wechatUserMapper.select();
    logger.info("----------------读出数据：" + i + "    用户数量：" + wechatUserList.size());
    int result = wechatUserMapper.update(wechatUser);
    logger.info("----------------修改数据：" + i + "  结果" + result);

结果：

两次大事务串行

« Linux常用命令

Why use Paxos? »

Copyright © 2016 Powered by MWeb,  Theme used GitHub CSS.