LoginSignup
3
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ch7821

WebSphereからMySQL,Db2,Oracle,SQLServerへDataSource接続（プロパティ確認リベンジ＆Final ）おまけ：MySQLのトランザクション分離とネクストキーロック

Last updated at Posted at 2018-02-04

前回の記事で、WASからlookup()して取得したDataSourceオブジェクト、およびDataSourceからgetConnection()して取得したConnectionオブジェクトが、WASのクラスだったということまでわかりました。前回不完全だったプロパティの値をきちんと取得できるようにすることと、JDBCドライバ固有のプロパティ値をどこにどのように持っているかを確認します。

クラス情報取得

PropertyDescriptorのgetValue()メソッドではプロパティ値を取得できないことがわかったので、getterをinvoke()して値を取得する方法に変更します。この場合、そもそもの値がnullだとNPEになるので、value=UNDEFINEDにしてスローした例外のgetMessage()を添えることにしました（Methodは"not null"だが、invoke()が返すObjectがnull。つまりそもそも初期化されていない（null）フィールド変数の場合、null.toString()となりNPE）。[本当ならObject obj = m.invoke();からif(obj != null)などとした方がよいかも]

ClassInfo.java
package net.mognet.servlet;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.util.LinkedHashMap;

public class ClassInfo {
	protected static final String NOT_AVAILABLE = "N/A";
	protected static final String UNKNOWN = "unkown";
    protected static final String UNDEFINED = "undefined";

	public static LinkedHashMap<String, String[]> getLinkedHashMap(Object obj) {
		LinkedHashMap<String, String[]> map = new LinkedHashMap<String, String[]>();
		BeanInfo bi = null;
		PropertyDescriptor[] pd = null;
		String name, type, value, getter, setter;
		Method m;

		
			try {
				bi = Introspector.getBeanInfo(obj.getClass());
			} catch (IntrospectionException e) {
				e.printStackTrace();
			}
			pd = bi.getPropertyDescriptors();
			for (int i = 0; i < pd.length; i++) {
				name = pd[i].getName();
				type = pd[i].getPropertyType().getName();
				m = pd[i].getReadMethod();
				if (m != null) {
					getter = m.getName();
					try {
						value= m.invoke(obj).toString();
					} catch (Exception e) {
						System.err.println("Exception occured while processing " + m.getName() + " --- " + e.getMessage());
						e.printStackTrace();
						value = UNDEFINED + " (getter throwed " + e.getMessage() + " )";
					}
				} else {
					getter = NOT_AVAILABLE;
					value = UNKNOWN + " (getter method is not available)";
				}
				m = pd[i].getWriteMethod();
				if (m != null) {
					setter = m.getName();
				} else {
					setter = NOT_AVAILABLE;
				}
				map.put(name, new String[] { type, getter, setter, value });
			}

		
		return map;
	}

	public static String exposeAsMarkdown(Object obj) {
		LinkedHashMap<String, String[]> map = getLinkedHashMap(obj);
		final String SEPARATOR = "|";
		final String HEADER = SEPARATOR + "name" + SEPARATOR + "type" + SEPARATOR + "getter" + SEPARATOR + "setter" + SEPARATOR
				+ "value" + SEPARATOR + "\n";
		String[] pi;
		StringBuilder sb = new StringBuilder(HEADER);
		sb.append("|:--|:--|:--|:--|:--|\n");
		for (String name : map.keySet()) {
			pi = map.get(name);
			sb.append(SEPARATOR + name + SEPARATOR + pi[0] + SEPARATOR + pi[1] + SEPARATOR + pi[2]
					+ SEPARATOR + pi[3] + SEPARATOR + "\n");
		}
		return sb.toString();
	}

	public static String exposeAsCSV(Object obj) {
		LinkedHashMap<String, String[]> map = getLinkedHashMap(obj);
		final String SEPARATOR = ",";
		final String HEADER = "name" + SEPARATOR + "type" + SEPARATOR + "getter" + SEPARATOR + "setter" + SEPARATOR
				+ "value\n";
		String[] pi;
		StringBuilder sb = new StringBuilder(HEADER);
		for (String name : map.keySet()) {
			pi = map.get(name);
			sb.append(name + SEPARATOR + pi[0] + SEPARATOR + pi[1] + SEPARATOR + pi[2]
					+ SEPARATOR + pi[3] + "\n");
		}
		return sb.toString();
	}

	public static String exposeAsHtmlTable(Object obj) {
		LinkedHashMap<String, String[]> map = getLinkedHashMap(obj);
		final String SEPARATOR = "</td><td>";
		final String HEADER = "<th>name</th><th>type</th><th>getter</th><th>setter</th><th>value</th>\n";
		String[] pi;
		StringBuilder sb = new StringBuilder(HEADER);
		for (String name : map.keySet()) {
			pi = map.get(name);
			sb.append("<tr><td>" + name + SEPARATOR + pi[0] + SEPARATOR + pi[1] + SEPARATOR
					+ pi[2] + SEPARATOR + pi[3] + "</td></tr>\n");
		}
		return sb.toString();
	}
}

サーブレット側のコードは変えていません。

JDBCProperty.java
package net.mognet.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.HashMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;


class JDBCType {
	private String className, jndiName;
	public JDBCType(String className, String jndiName) {
		this.className = className;
		this.jndiName = jndiName;
	}
	public String getClassName() {
		return this.className;
	}
	public String getJndiName() {
		return this.jndiName;
	}
}

@WebServlet({ "/JDBCProperty", "/*" })
public class JDBCProperty extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public JDBCProperty() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		HashMap<String, JDBCType> map = new HashMap<String, JDBCType>();
		map.put("MySQL", new JDBCType("com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource", "jdbc/mysqlmaster"));
		map.put("Db2", new JDBCType("com.ibm.db2.jcc.DB2ConnectionPoolDataSource", "jdbc/db2"));
		map.put("Oracle", new JDBCType("oracle.jdbc.pool.OracleConnectionPoolDataSource", "jdbc/oracle"));
		map.put("SQLServer", new JDBCType("com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource", "jdbc/sqlserver"));
		PrintWriter out = response.getWriter();
		response.setContentType("text/html; charset=UTF-8");
		out.println("<html><head><title>jdbc property</title></head><body>");
		
		try {
			Context ctx = new InitialContext();
			for(String key : map.keySet()) {
				DataSource ds = (DataSource) ctx.lookup(map.get(key).getJndiName());
				String title = key + "(" + ds.getClass().getName() + ")";
				out.println("<h1>" + title + "</h1>");
				String prop = ClassInfo.exposeAsMarkdown(ds);
				out.println("<pre>");
				out.println("# " + title);
				out.println(prop);
				out.println("</pre>");
				
				java.sql.Connection cn = ds.getConnection();
				title = key + "(" + cn.getClass().getName() + ")";
				out.println("<h1>" + title + "</h1>");
				prop = ClassInfo.exposeAsMarkdown(cn);
				out.println("<pre>");
				out.println("# " + title + "</br>");
				out.println(prop);
				out.println("</pre>");
			}
		} catch (NamingException | SQLException | IllegalArgumentException e) {
			out.println("<p>" + e.getMessage() + "</p>");
		}
		out.println("</body></html>");
	}

}

結果

取得した結果です。

Db2(com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource)

name type getter setter value
XADataSource boolean isXADataSource N/A false
active boolean isActive N/A undefined (getter throwed null )
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
connection java.sql.Connection getConnection N/A com.ibm.ws.rsadapter.jdbc.WSJccSQLJPDQConnection@d1075528
containerManagedConnection java.sql.Connection getContainerManagedConnection N/A com.ibm.ws.rsadapter.jdbc.WSJccSQLJPDQConnection@244bf097
dataStoreHelper com.ibm.websphere.rsadapter.DataStoreHelper getDataStoreHelper N/A com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper@58fc7511
heterogeneousPoolingConfigured boolean isHeterogeneousPoolingConfigured N/A false
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalDB2UniversalDataStoreHelper@ad729dd3
logWriter java.io.PrintWriter getLogWriter setLogWriter undefined (getter throwed null )
loginTimeout int getLoginTimeout setLoginTimeout 0
parentLogger java.util.logging.Logger getParentLogger N/A undefined (getter throwed null )
reference javax.naming.Reference getReference setReference undefined (getter throwed null )

Db2(com.ibm.ws.rsadapter.jdbc.WSJccSQLJPDQConnection)

name type getter setter value
CRI javax.resource.spi.ConnectionRequestInfo getCRI N/A undefined (getter throwed null )
DB2Object java.lang.Object getDB2Object N/A com.ibm.db2.jcc.am.jf@b25d50d1
JNDIName java.lang.String getJNDIName N/A jdbc/db2
SQLJLogWriter com.ibm.db2.jcc.SQLJLogWriter getSQLJLogWriter N/A com.ibm.db2.jcc.t4.g@480047e3
aborted boolean isAborted setAborted false
autoCommit boolean getAutoCommit setAutoCommit true
catalog java.lang.String getCatalog setCatalog undefined (getter throwed null )
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJccSQLJPDQConnection
clientInfo java.util.Properties getClientInfo setClientInfo undefined (getter throwed null )
clientInformation java.util.Properties getClientInformation setClientInformation undefined (getter throwed null )
closed boolean isClosed N/A false
connectionContext sqlj.runtime.ref.DefaultContext getConnectionContext N/A sqlj.runtime.ref.DefaultContext@ec369231
currentAutoCommit boolean N/A setCurrentAutoCommit unkown (getter method is not available)
currentTransactionIsolation int N/A setCurrentTransactionIsolation unkown (getter method is not available)
databaseName java.lang.String getDatabaseName N/A MYDB
helper com.ibm.websphere.rsadapter.DataStoreHelper getHelper N/A com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper@58fc7511
holdability int getHoldability setHoldability 2
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalDB2UniversalDataStoreHelper@ad729dd3
isMonitoring boolean N/A setIsMonitoring unkown (getter method is not available)
j2EEName com.ibm.websphere.csi.J2EEName getJ2EEName setJ2EEName JDBCProp#JDBCProp.war#net.mognet.servlet.JDBCProperty
jccDriver boolean isJccDriver N/A true
metaData java.sql.DatabaseMetaData getMetaData N/A com.ibm.ws.rsadapter.jdbc.WSJdbcDatabaseMetaData@b8fc117d
monitoring boolean isMonitoring N/A false
networkTimeout int getNetworkTimeout N/A undefined (getter throwed null )
readOnly boolean isReadOnly setReadOnly false
reserved boolean isReserved N/A false
schema java.lang.String getSchema setSchema undefined (getter throwed null )
shareable boolean isShareable N/A true
state int getState N/A 0
stateString java.lang.String getStateString N/A ACTIVE
systemMonitor com.ibm.websphere.rsadapter.WSSystemMonitor getSystemMonitor N/A com.ibm.websphere.rsadapter.WSSystemMonitor@1a666605
tracer com.ibm.ejs.ras.TraceComponent getTracer N/A com.ibm.ejs.ras.TraceComponent@5bc08027
transactionIsolation int getTransactionIsolation setTransactionIsolation 4
typeMap java.util.Map getTypeMap setTypeMap {}
warnings java.sql.SQLWarning getWarnings N/A undefined (getter throwed null )
webSphereVersion java.lang.String getWebSphereVersion N/A 6.0

MySQL(com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource)

name type getter setter value
XADataSource boolean isXADataSource N/A false
active boolean isActive N/A undefined (getter throwed null )
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
connection java.sql.Connection getConnection N/A com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@643efc93
containerManagedConnection java.sql.Connection getContainerManagedConnection N/A com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@54cdd8bb
dataStoreHelper com.ibm.websphere.rsadapter.DataStoreHelper getDataStoreHelper N/A com.ibm.websphere.rsadapter.GenericDataStoreHelper@ebc0e4e8
heterogeneousPoolingConfigured boolean isHeterogeneousPoolingConfigured N/A false
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper@5a0ab59e
logWriter java.io.PrintWriter getLogWriter setLogWriter undefined (getter throwed null )
loginTimeout int getLoginTimeout setLoginTimeout 0
parentLogger java.util.logging.Logger getParentLogger N/A undefined (getter throwed null )
reference javax.naming.Reference getReference setReference undefined (getter throwed null )

MySQL(com.ibm.ws.rsadapter.jdbc.WSJdbcConnection)

name type getter setter value
CRI javax.resource.spi.ConnectionRequestInfo getCRI N/A undefined (getter throwed null )
JNDIName java.lang.String getJNDIName N/A jdbc/mysqlmaster
aborted boolean isAborted setAborted false
autoCommit boolean getAutoCommit setAutoCommit true
catalog java.lang.String getCatalog setCatalog
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
clientInfo java.util.Properties getClientInfo setClientInfo {}
clientInformation java.util.Properties getClientInformation setClientInformation undefined (getter throwed null )
closed boolean isClosed N/A false
helper com.ibm.websphere.rsadapter.DataStoreHelper getHelper N/A com.ibm.websphere.rsadapter.GenericDataStoreHelper@ebc0e4e8
holdability int getHoldability setHoldability 2
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper@5a0ab59e
j2EEName com.ibm.websphere.csi.J2EEName getJ2EEName setJ2EEName JDBCProp#JDBCProp.war#net.mognet.servlet.JDBCProperty
metaData java.sql.DatabaseMetaData getMetaData N/A com.ibm.ws.rsadapter.jdbc.WSJdbcDatabaseMetaData@5d4a2f00
networkTimeout int getNetworkTimeout N/A 0
readOnly boolean isReadOnly setReadOnly false
reserved boolean isReserved N/A false
schema java.lang.String getSchema setSchema undefined (getter throwed null )
shareable boolean isShareable N/A true
state int getState N/A 0
stateString java.lang.String getStateString N/A ACTIVE
systemMonitor com.ibm.websphere.rsadapter.WSSystemMonitor getSystemMonitor N/A undefined (getter throwed null )
transactionIsolation int getTransactionIsolation setTransactionIsolation 2
typeMap java.util.Map getTypeMap setTypeMap {}
warnings java.sql.SQLWarning getWarnings N/A undefined (getter throwed null )

Oracle(com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource)

name type getter setter value
XADataSource boolean isXADataSource N/A false
active boolean isActive N/A undefined (getter throwed null )
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
connection java.sql.Connection getConnection N/A com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@29df4c18
containerManagedConnection java.sql.Connection getContainerManagedConnection N/A com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@c7f34c7e
dataStoreHelper com.ibm.websphere.rsadapter.DataStoreHelper getDataStoreHelper N/A com.ibm.websphere.rsadapter.Oracle11gDataStoreHelper@98c985fc
heterogeneousPoolingConfigured boolean isHeterogeneousPoolingConfigured N/A false
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalOracle11gDataStoreHelper@cc38870a
logWriter java.io.PrintWriter getLogWriter setLogWriter undefined (getter throwed null )
loginTimeout int getLoginTimeout setLoginTimeout 0
parentLogger java.util.logging.Logger getParentLogger N/A undefined (getter throwed null )
reference javax.naming.Reference getReference setReference undefined (getter throwed null )

Oracle(com.ibm.ws.rsadapter.jdbc.WSJdbcConnection)

name type getter setter value
CRI javax.resource.spi.ConnectionRequestInfo getCRI N/A undefined (getter throwed null )
JNDIName java.lang.String getJNDIName N/A jdbc/oracle
aborted boolean isAborted setAborted false
autoCommit boolean getAutoCommit setAutoCommit true
catalog java.lang.String getCatalog setCatalog undefined (getter throwed null )
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
clientInfo java.util.Properties getClientInfo setClientInfo {}
clientInformation java.util.Properties getClientInformation setClientInformation undefined (getter throwed null )
closed boolean isClosed N/A false
helper com.ibm.websphere.rsadapter.DataStoreHelper getHelper N/A com.ibm.websphere.rsadapter.Oracle11gDataStoreHelper@98c985fc
holdability int getHoldability setHoldability 1
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalOracle11gDataStoreHelper@cc38870a
j2EEName com.ibm.websphere.csi.J2EEName getJ2EEName setJ2EEName JDBCProp#JDBCProp.war#net.mognet.servlet.JDBCProperty
metaData java.sql.DatabaseMetaData getMetaData N/A com.ibm.ws.rsadapter.jdbc.WSJdbcDatabaseMetaData@1e2424da
networkTimeout int getNetworkTimeout N/A 0
readOnly boolean isReadOnly setReadOnly false
reserved boolean isReserved N/A false
schema java.lang.String getSchema setSchema ORAMASTER
shareable boolean isShareable N/A true
state int getState N/A 0
stateString java.lang.String getStateString N/A ACTIVE
systemMonitor com.ibm.websphere.rsadapter.WSSystemMonitor getSystemMonitor N/A undefined (getter throwed null )
transactionIsolation int getTransactionIsolation setTransactionIsolation 2
typeMap java.util.Map getTypeMap setTypeMap {}
warnings java.sql.SQLWarning getWarnings N/A undefined (getter throwed null )

SQLServer(com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource)

name type getter setter value
XADataSource boolean isXADataSource N/A false
active boolean isActive N/A undefined (getter throwed null )
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
connection java.sql.Connection getConnection N/A com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@59e24f6e
containerManagedConnection java.sql.Connection getContainerManagedConnection N/A com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@de49784d
dataStoreHelper com.ibm.websphere.rsadapter.DataStoreHelper getDataStoreHelper N/A com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelper@365d0d65
heterogeneousPoolingConfigured boolean isHeterogeneousPoolingConfigured N/A false
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalMicrosoftSQLServerDataStoreHelper@744356bd
logWriter java.io.PrintWriter getLogWriter setLogWriter undefined (getter throwed null )
loginTimeout int getLoginTimeout setLoginTimeout 15
parentLogger java.util.logging.Logger getParentLogger N/A undefined (getter throwed null )
reference javax.naming.Reference getReference setReference undefined (getter throwed null )

SQLServer(com.ibm.ws.rsadapter.jdbc.WSJdbcConnection)

name type getter setter value
CRI javax.resource.spi.ConnectionRequestInfo getCRI N/A undefined (getter throwed null )
JNDIName java.lang.String getJNDIName N/A jdbc/sqlserver
aborted boolean isAborted setAborted false
autoCommit boolean getAutoCommit setAutoCommit true
catalog java.lang.String getCatalog setCatalog master
class java.lang.Class getClass N/A class com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
clientInfo java.util.Properties getClientInfo setClientInfo {}
clientInformation java.util.Properties getClientInformation setClientInformation undefined (getter throwed null )
closed boolean isClosed N/A false
helper com.ibm.websphere.rsadapter.DataStoreHelper getHelper N/A com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelper@365d0d65
holdability int getHoldability setHoldability 1
internalDataStoreHelper com.ibm.ws.rsadapter.spi.InternalDataStoreHelper getInternalDataStoreHelper N/A com.ibm.ws.rsadapter.spi.InternalMicrosoftSQLServerDataStoreHelper@744356bd
j2EEName com.ibm.websphere.csi.J2EEName getJ2EEName setJ2EEName JDBCProp#JDBCProp.war#net.mognet.servlet.JDBCProperty
metaData java.sql.DatabaseMetaData getMetaData N/A com.ibm.ws.rsadapter.jdbc.WSJdbcDatabaseMetaData@f24763f8
networkTimeout int getNetworkTimeout N/A undefined (getter throwed null )
readOnly boolean isReadOnly setReadOnly false
reserved boolean isReserved N/A false
schema java.lang.String getSchema setSchema dbo
shareable boolean isShareable N/A true
state int getState N/A 0
stateString java.lang.String getStateString N/A ACTIVE
systemMonitor com.ibm.websphere.rsadapter.WSSystemMonitor getSystemMonitor N/A undefined (getter throwed null )
transactionIsolation int getTransactionIsolation setTransactionIsolation 4
typeMap java.util.Map getTypeMap setTypeMap {}
warnings java.sql.SQLWarning getWarnings N/A undefined (getter throwed null )

JDBCドライバ特有のプロパティはほとんど隠蔽されるようです（MySQL用DataSourceのカスタムプロパティにいくつか値をいれてみましたが、ConnectionのclientInfoの値変わらず）。これらを眺めているとどんな管理をしているのかがなんとなくみえてきたりしますね。autoCommitをOFFにしたりreadOnlyをONにしてみたりすることで、どんな動きをするのかとか。

トランザクション分離レベル

特に、トランザクション分離レベル（transactionIsolation）はDBエンジン特有の初期値になっています。

|DB|レベル|java.sql.connectionの定数1|ロストアップデート|ダーティーリード|ノンリピータブルリード|ファントムリード|
|:--|:--|:--|:--|:--|:--|:--|:--|
|Db2|4|TRANSACTION_REPEATABLE_READ|x|x|x|o|
|MySQL|2|TRANSACTION_READ_COMMITTED|x|x|o|o|
|Oracle|2| TRANSACTION_READ_COMMITTED |x|x|o|o|
|SQL Server|4| TRANSACTION_REPEATABLE_READ |x|x|x|o|

ロストアップデート
後勝ちになって前の更新が失われる。
ダーティリード
コミットされていない更新も読込む。
ノンリビータブルリード
ある値を同一トランザクションから参照する際に、常に同じ値がかえるとは限らない。
ファントムリード
ノンリピータブルリードと似ているが、こちらは行単位で、さっきは存在していなかったものが見える。

MySQLのネクストキーロック

ただし、MySQLにはネクストキーロックというInnoDBのインデックスロック機構が働くので、一部ファントムリードが発生しない可能性もあります。

例）HogeTable

ikey value
1 hogevalue1
3 hogevalue3
5 hogevalue4

このとき、トランザクションT1とT2が以下のような振る舞いをしたとします（無理やり感はご容赦）。

T1
START TRANSACTION;
SELECT count(*) FROM HogeTable;
SELECT value FROM HogeTable WHERE ikey = '3' FOR UPDATE;
/* なんか足し算とかで時間がかかる処理 */
UPDATE HogeTable SET value = 'newhogevalue3' WHERE ikey = '3';
SELECT count(*) FROM HogeTable;
COMMIT;
T2
START TRANSACTION;
SELECT count(*) FROM HogeTable;
INSERT INTO HogeTable values ( '2', 'hogevalue2' );
SELECT count(*) FROM HogeTable;
COMMIT;

T1が開始され、「なんか足し算とかで時間がかかる処理」をしている間にT2が開始されたとします。そして、T1がDBを更新するより前にT2がCOMMITしたとしても、T1のSELECT count(*)ステートメントは「３」を返します。これはネクストキーロックによって、(negative_max-3]および(3-intmax)（"("と")"は端点含まず、"["と"]"は端点含む）までインデックスロックがかかるためです。T1トランザクション内部的にロックがかかりますので、T2によるikey=2のINSERTの影響を受けません。T2はT2で独立して動作します（イメージ的にはロックしたタイミングで、インデックスのスナップショートデータを保持しているようなものです）。インデックスのみなので、他のトランザクションが値を変更してCOMMITした場合、その変更内容が読み取られます（READ_COMMITTED）。

T2-α
START TRANSACTION;
INSERT INTO HogeTable values ( '0', 'hogevalue0' );
INSERT INTO HogeTable values ( '6', 'hogevalue6' );
SELECT count(*) FROM HogeTable;
UPDATE HogeTable SET value = 'newhogevalue1' WHERE ikey = '1';
UPDATE HogeTable SET value = 'newhogevalue5' WHERE ikey = '5';
COMMIT;

表に示した初期状態から同じように、T1が「なんか足し算とかで時間がかかる処理」をしている間にT2-αが開始されたとします。T2-αがCOMMITするとT2-αによるSELECT count(*)ステートメントは「５」を返します。一方、T1ではやはり「３」が返ります。T1内でCOMMITする前に、SELECT * FROM HogeTable WHERE ikey = '1';とかすると、ちゃんと"newhogevalue1"が、WHERE ikey = '5'なら"newhogevalue5"がそれぞれ返ります。ただし、くどいようですがインデックスのみロックがかかっているので、追加した行は参照できません。T1内でSELECT * FROM HogeTable WHERE ikey = '0';はnull（Empty set）を返します。

話がだいぶそれたので、今回はこれくらいにしておきます。

  1. Java 8 API Document
    念のため補足。 ↩

3
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?