赞
踩
MySQL数据库默认事务隔离级别为
REPEATABLE-READ
,大型互联网公司一般建议改成READ-COMMITTED
,通过降低间隙锁概率,以提升事务性能。(顺便提一句,Oracle数据库默认事务隔离级别是READ-COMMITTED
)
show variables like '%tx_isolation%';
注: 使用DBeaver
数据库连接工具查询事务隔离级别时,要确认是否修改过工具的默认事务隔离级别,若修改过DBeaver
数据库连接工具的默认事务隔离级别,查询结果可能和预期的不一致。因此建议使用原生的mysql cmd
工具查看事务隔离级别。
JDBC连接的事务隔离级别默认取数据库服务端设置的默认隔离级别,也就是
show variables like '%tx_isolation%'
查询结果
以
MyBatis
框架为例,Debug跟踪当前SqlSession,查看org.apache.ibatis.session.SqlSession#getConnection
对象使用的原生Connection
对象(com.mysql.cj.jdbc.ConnectionImpl
)查看isolationLevel
属性值即可!
/** * A constant indicating that transactions are not supported. */ int TRANSACTION_NONE = 0; /** * A constant indicating that * dirty reads, non-repeatable reads and phantom reads can occur. * This level allows a row changed by one transaction to be read * by another transaction before any changes in that row have been * committed (a "dirty read"). If any of the changes are rolled back, * the second transaction will have retrieved an invalid row. */ int TRANSACTION_READ_UNCOMMITTED = 1; /** * A constant indicating that * dirty reads are prevented; non-repeatable reads and phantom * reads can occur. This level only prohibits a transaction * from reading a row with uncommitted changes in it. */ int TRANSACTION_READ_COMMITTED = 2; /** * A constant indicating that * dirty reads and non-repeatable reads are prevented; phantom * reads can occur. This level prohibits a transaction from * reading a row with uncommitted changes in it, and it also * prohibits the situation where one transaction reads a row, * a second transaction alters the row, and the first transaction * rereads the row, getting different values the second time * (a "non-repeatable read"). */ int TRANSACTION_REPEATABLE_READ = 4; /** * A constant indicating that * dirty reads, non-repeatable reads and phantom reads are prevented. * This level includes the prohibitions in * <code>TRANSACTION_REPEATABLE_READ</code> and further prohibits the * situation where one transaction reads all rows that satisfy * a <code>WHERE</code> condition, a second transaction inserts a row that * satisfies that <code>WHERE</code> condition, and the first transaction * rereads for the same condition, retrieving the additional * "phantom" row in the second read. */ int TRANSACTION_SERIALIZABLE = 8; /** * Attempts to change the transaction isolation level for this * <code>Connection</code> object to the one given. * The constants defined in the interface <code>Connection</code> * are the possible transaction isolation levels. * <P> * <B>Note:</B> If this method is called during a transaction, the result * is implementation-defined. * * @param level one of the following <code>Connection</code> constants: * <code>Connection.TRANSACTION_READ_UNCOMMITTED</code>, * <code>Connection.TRANSACTION_READ_COMMITTED</code>, * <code>Connection.TRANSACTION_REPEATABLE_READ</code>, or * <code>Connection.TRANSACTION_SERIALIZABLE</code>. * (Note that <code>Connection.TRANSACTION_NONE</code> cannot be used * because it specifies that transactions are not supported.) * @exception SQLException if a database access error occurs, this * method is called on a closed connection * or the given parameter is not one of the <code>Connection</code> * constants * @see DatabaseMetaData#supportsTransactionIsolationLevel * @see #getTransactionIsolation */ void setTransactionIsolation(int level) throws SQLException;
JDBC SPI加载机制
com.mysql.cj.jdbc.ConnectionImpl#ConnectionImpl(com.mysql.cj.conf.HostInfo)
==>
com.mysql.cj.jdbc.ConnectionImpl#createNewIO
==>
com.mysql.cj.jdbc.ConnectionImpl#connectOneTryOnly
==>
com.mysql.cj.jdbc.ConnectionImpl#initializePropsFromServer
==> 查询服务端变量
transaction_isolation
(别名)和tx_isolation
com.mysql.cj.jdbc.ConnectionImpl#checkTransactionIsolationLevel
/** * Set transaction isolation level to the value received from server if any. * Is called by connectionInit(...) */ private void checkTransactionIsolationLevel() { String s = this.session.getServerSession().getServerVariable("transaction_isolation"); if (s == null) { s = this.session.getServerSession().getServerVariable("tx_isolation"); } if (s != null) { Integer intTI = mapTransIsolationNameToValue.get(s); if (intTI != null) { this.isolationLevel = intTI.intValue(); } } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
com.mysql.cj.NativeSession#loadServerVariables
/** * Loads the result of 'SHOW VARIABLES' into the serverVariables field so * that the driver can configure itself. * * @param syncMutex * synchronization mutex * @param version * driver version string */ public void loadServerVariables(Object syncMutex, String version) { if (this.cacheServerConfiguration.getValue()) { createConfigCacheIfNeeded(syncMutex); Map<String, String> cachedVariableMap = this.serverConfigCache.get(this.hostInfo.getDatabaseUrl()); if (cachedVariableMap != null) { String cachedServerVersion = cachedVariableMap.get(SERVER_VERSION_STRING_VAR_NAME); if (cachedServerVersion != null && getServerSession().getServerVersion() != null && cachedServerVersion.equals(getServerSession().getServerVersion().toString())) { this.protocol.getServerSession().setServerVariables(cachedVariableMap); return; } this.serverConfigCache.invalidate(this.hostInfo.getDatabaseUrl()); } } try { if (version != null && version.indexOf('*') != -1) { StringBuilder buf = new StringBuilder(version.length() + 10); for (int i = 0; i < version.length(); i++) { char c = version.charAt(i); buf.append(c == '*' ? "[star]" : c); } version = buf.toString(); } String versionComment = (this.propertySet.getBooleanProperty(PropertyDefinitions.PNAME_paranoid).getValue() || version == null) ? "" : "/* " + version + " */"; this.protocol.getServerSession().setServerVariables(new HashMap<String, String>()); if (versionMeetsMinimum(5, 1, 0)) { StringBuilder queryBuf = new StringBuilder(versionComment).append("SELECT"); queryBuf.append(" @@session.auto_increment_increment AS auto_increment_increment"); queryBuf.append(", @@character_set_client AS character_set_client"); queryBuf.append(", @@character_set_connection AS character_set_connection"); queryBuf.append(", @@character_set_results AS character_set_results"); queryBuf.append(", @@character_set_server AS character_set_server"); queryBuf.append(", @@collation_server AS collation_server"); queryBuf.append(", @@init_connect AS init_connect"); queryBuf.append(", @@interactive_timeout AS interactive_timeout"); if (!versionMeetsMinimum(5, 5, 0)) { queryBuf.append(", @@language AS language"); } queryBuf.append(", @@license AS license"); queryBuf.append(", @@lower_case_table_names AS lower_case_table_names"); queryBuf.append(", @@max_allowed_packet AS max_allowed_packet"); queryBuf.append(", @@net_write_timeout AS net_write_timeout"); if (!versionMeetsMinimum(8, 0, 3)) { queryBuf.append(", @@query_cache_size AS query_cache_size"); queryBuf.append(", @@query_cache_type AS query_cache_type"); } queryBuf.append(", @@sql_mode AS sql_mode"); queryBuf.append(", @@system_time_zone AS system_time_zone"); queryBuf.append(", @@time_zone AS time_zone"); if (versionMeetsMinimum(8, 0, 3) || (versionMeetsMinimum(5, 7, 20) && !versionMeetsMinimum(8, 0, 0))) { queryBuf.append(", @@transaction_isolation AS transaction_isolation"); } else { queryBuf.append(", @@tx_isolation AS transaction_isolation"); } queryBuf.append(", @@wait_timeout AS wait_timeout"); NativePacketPayload resultPacket = sendCommand(this.commandBuilder.buildComQuery(null, queryBuf.toString()), false, 0); Resultset rs = ((NativeProtocol) this.protocol).readAllResults(-1, false, resultPacket, false, null, new ResultsetFactory(Type.FORWARD_ONLY, null)); Field[] f = rs.getColumnDefinition().getFields(); if (f.length > 0) { ValueFactory<String> vf = new StringValueFactory(f[0].getEncoding()); Row r; if ((r = rs.getRows().next()) != null) { for (int i = 0; i < f.length; i++) { this.protocol.getServerSession().getServerVariables().put(f[i].getColumnLabel(), r.getValue(i, vf)); } } } } else { NativePacketPayload resultPacket = sendCommand(this.commandBuilder.buildComQuery(null, versionComment + "SHOW VARIABLES"), false, 0); Resultset rs = ((NativeProtocol) this.protocol).readAllResults(-1, false, resultPacket, false, null, new ResultsetFactory(Type.FORWARD_ONLY, null)); ValueFactory<String> vf = new StringValueFactory(rs.getColumnDefinition().getFields()[0].getEncoding()); Row r; while ((r = rs.getRows().next()) != null) { this.protocol.getServerSession().getServerVariables().put(r.getValue(0, vf), r.getValue(1, vf)); } } } catch (PasswordExpiredException ex) { if (this.disconnectOnExpiredPasswords.getValue()) { throw ex; } } catch (IOException e) { throw ExceptionFactory.createException(e.getMessage(), e); } if (this.cacheServerConfiguration.getValue()) { // 缓存服务端变量 this.protocol.getServerSession().getServerVariables().put(SERVER_VERSION_STRING_VAR_NAME, getServerSession().getServerVersion().toString()); this.serverConfigCache.put(this.hostInfo.getDatabaseUrl(), this.protocol.getServerSession().getServerVariables()); } }
上面代码本质是执行
select
语句查询MySQL服务端变量
select @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
java.sql.Connection#TRANSACTION_REPEATABLE_READ
com.alibaba.druid.pool.DruidAbstractDataSource#defaultTransactionIsolation
druid:
defaultTransactionIsolation: 2
com.zaxxer.hikari.HikariConfig#transactionIsolationName
hikari:
transactionIsolationName: TRANSACTION_READ_COMMITTED
@Transactional(isolation = Isolation.READ_COMMITTED)
参考:
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。