Hi *,
I have a problem concerning transaction locking using multiple
concurrent transactions. In one transaction a new row is inserted
and before the commit a query in another transaction is executed.
For the second transaction I expect to see the state without the
new row, but in SAP DB I get a lock exception. It seems, that an
insert will give a table lock.
Changing the transaction isolation level to one of REPEATABLE_READ
or SERIALIZABLE will throw the lock exception too.
Using READ_UNCOMMITTED will not throw an exception, but does not
give the desired result.
How can the given problem be solved ?
(See test program and output from PostgreSQL and SAP DB.)
Sincerely,
Dirk Z�ttl
_____________________________________________________________________
// $Header: /cvsroot/javasrc/de/tangro/sql/IsolationTest.java,v 1.1 2003/01/18 17:45:08 dz Exp $
// copyright � 1997 - 2003 tangro software components gmbh - all rights reserved.
package de.tangro.sql;
import java.sql.*;
/**
* Test database transaction isolation.
*/
public class IsolationTest {
/** CVS $Revision: 1.1 $ */
public static final String versId = "$Revision: 1.1 $";
public static void main(String[] args) throws Exception {
String driver = "com.sap.dbtech.jdbc.DriverSapDB";
String url = "jdbc:sapdb:TANGRO?transport=native";
String user = "dba";
String password = "dba";
// driver = "org.postgresql.Driver";
// url = "jdbc:postgresql://localhost:5432/dz";
//--- load driver ---
Class.forName(driver);
//--- connect to database ---
Connection conn = getConnection(url, user, password);
//--- display info ---
String info = getDbInfo(conn, true);
System.out.println(info);
//--- setup ---
Statement stmt = conn.createStatement();
try {
stmt.execute("DROP TABLE xyz");
conn.commit();
} catch (SQLException ex) {
//--- reconnect required for PostgreSQL ---
// stmt.close();
// conn.close();
// conn = getConnection(url, user, password);
// stmt = conn.createStatement();
}
stmt.execute("CREATE TABLE xyz (id INTEGER PRIMARY KEY, txt VARCHAR(30), val INTEGER, total INTEGER)");
conn.commit();
stmt.executeUpdate("INSERT INTO xyz (id,txt,val,total) values (1, 'a', 2, 2)");
stmt.executeUpdate("INSERT INTO xyz (id,txt,val,total) values (2, 'b', 3, 5)");
stmt.close();
conn.commit();
conn.close();
//--- create transactions ---
Connection ta1 = getConnection(url, user, password);
Connection ta2 = getConnection(url, user, password);
Statement stmt1 = ta1.createStatement();
Statement stmt2 = ta2.createStatement();
ResultSet rset1, rset2;
//--- insert new row on transaction 1 ---
System.out.println("insert ta-1 ...");
stmt1.executeUpdate("INSERT INTO xyz (id,txt,val) values (3, 'c', 5)");
System.out.println("insert ta-1 OK");
//--- select on transaction 2 ---
System.out.println("select ta-2 ...");
rset2 = stmt2.executeQuery("SELECT txt FROM xyz");
while (rset2.next()) {
String txt = rset2.getString(1);
System.out.println(txt);
}
rset2.close();
stmt2.close();
ta2.rollback();
System.out.println("select ta-2 OK");
//--- update on transaction 1 ---
System.out.println("select ta-1 ...");
rset1 = stmt1.executeQuery("SELECT SUM(val) FROM xyz");
rset1.next();
int total = rset1.getInt(1);
rset1.close();
System.out.println("select ta-1 OK");
System.out.println("update ta-1 ...");
stmt1.executeUpdate("UPDATE xyz SET total=" + total + " WHERE id=3");
ta1.commit();
System.out.println("update ta-1 OK");
//--- cleanup ---
ta1.close();
ta2.close();
}
private static Connection getConnection(String url, String user, String password) throws SQLException {
Connection conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);
conn.setReadOnly(false);
// conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
return conn;
}
/** Get database info. */
private static String getDbInfo(Connection conn, boolean trace) {
//--- check connection ---
if (conn == null)
return " conn == null\n";
String str = null;
try {
if (conn.isClosed())
str = " connection closed\n";
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
str = " " + ex.getMessage();
}
if (str != null)
return str;
//--- show connection settings ---
StringBuffer sb = new StringBuffer(500);
boolean bool;
//--- read-only ---
sb.append(" isReadOnly : ");
try {
bool = conn.isReadOnly();
sb.append(bool);
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- auto-commit ---
sb.append(" getAutoCommit : ");
try {
bool = conn.getAutoCommit();
sb.append(bool);
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- current catalog ---
sb.append(" getCatalog : ");
try {
str = conn.getCatalog();
sb.append(getLiteral(str, '"'));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- read database meta data ---
DatabaseMetaData meta = null;
try {
meta = conn.getMetaData();
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(" getMetaData : ");
sb.append(ex.getMessage());
sb.append('\n');
}
if (meta != null) {
//--- database URL ---
sb.append(" getURL : ");
try {
str = meta.getURL();
sb.append(getLiteral(str, '"'));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- database user ---
sb.append(" getUserName : ");
try {
str = meta.getUserName();
sb.append(getLiteral(str, '"'));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- driver name ---
sb.append(" getDriverName : ");
try {
str = meta.getDriverName();
sb.append(getLiteral(str, '"'));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- driver version ---
sb.append(" getDriverVersion : ");
try {
str = meta.getDriverVersion();
sb.append(getLiteral(str, '"'));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append("\n getDriverMajorVersion : ");
sb.append(meta.getDriverMajorVersion());
sb.append("\n getDriverMinorVersion : ");
sb.append(meta.getDriverMinorVersion());
sb.append('\n');
//--- database product name ---
sb.append(" getDatabaseProductName : ");
try {
str = meta.getDatabaseProductName();
sb.append(getLiteral(str, '"'));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- database product version ---
sb.append(" getDatabaseProductVersion : ");
try {
str = meta.getDatabaseProductVersion();
sb.append(getLiteral(str, '"'));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- quote string ---
sb.append(" getIdentifierQuoteString : ");
try {
str = meta.getIdentifierQuoteString();
sb.append(str);
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- maximum number of columns in table ---
sb.append(" getMaxColumnsInTable : ");
try {
int max = meta.getMaxColumnsInTable();
sb.append(max);
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- maximum number of connections ---
sb.append(" getMaxConnections : ");
try {
int max = meta.getMaxConnections();
sb.append(max);
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- maximum number of statements (per connection ?) ---
sb.append(" getMaxStatements : ");
try {
int max = meta.getMaxStatements();
sb.append(max);
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
//--- default transaction isolation level ---
sb.append(" getDefaultTransactionIsolation : ");
try {
int txi = meta.getDefaultTransactionIsolation();
sb.append(getString(txi, TRANSACTION_ISOLATION_CODES, TRANSACTION_ISOLATION_STRINGS));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
}
//--- current transaction isolation level ---
sb.append(" getTransactionIsolation : ");
try {
int txi = conn.getTransactionIsolation();
sb.append(getString(txi, TRANSACTION_ISOLATION_CODES, TRANSACTION_ISOLATION_STRINGS));
} catch (SQLException ex) {
if (trace)
ex.printStackTrace();
sb.append(ex.getMessage());
}
sb.append('\n');
return sb.substring(0);
}
private static int[] TRANSACTION_ISOLATION_CODES = {
Connection.TRANSACTION_NONE,
Connection.TRANSACTION_READ_UNCOMMITTED,
Connection.TRANSACTION_READ_COMMITTED,
Connection.TRANSACTION_REPEATABLE_READ,
Connection.TRANSACTION_SERIALIZABLE,
};
private static String[] TRANSACTION_ISOLATION_STRINGS = {
"TRANSACTION_NONE",
"TRANSACTION_READ_UNCOMMITTED",
"TRANSACTION_READ_COMMITTED",
"TRANSACTION_REPEATABLE_READ",
"TRANSACTION_SERIALIZABLE",
};
//--- string helper functions ------------------------------------------------------------------
/** Map an integer key to a string. */
private static String getString(int value, int[] keys, String[] strings) {
int i, len = keys.length;
for (i = 0; (i < len) && (value != keys[i]); i++) continue;
return (i < strings.length) ? strings[i] : Integer.toString(value);
}
/** Get a string literal. */
private static String getLiteral(String string) {
if (string == null)
return "null";
StringBuffer sb = new StringBuffer(string.length() + 16);
appendLiteral(sb, string);
return sb.substring(0);
}
private static String getLiteral(String string, int quote) {
return getLiteral(string, quote, quote);
}
private static String getLiteral(String string, int leftQuote, int rightQuote) {
if (string == null)
return "null";
StringBuffer sb = new StringBuffer(string.length() + 20);
if (leftQuote >= 0)
sb.append((char) leftQuote);
appendLiteral(sb, string);
if (rightQuote >= 0)
sb.append((char) rightQuote);
return sb.substring(0);
}
private static void appendLiteral(StringBuffer sb, String string) {
int n = string.length() - 1;
if (n < 0)
return;
for (int i = 0; i < n; i++)
appendLiteral(sb, string.charAt(i), string.charAt(n));
appendLiteral(sb, string.charAt(n), '\0');
}
private static void appendLiteral(StringBuffer sb, char ch, char next) {
switch (ch) {
case '\b':
sb.append("\\b");
return;
case '\t':
sb.append("\\t");
return;
case '\n':
sb.append("\\n");
return;
case '\f':
sb.append("\\f");
return;
case '\r':
sb.append("\\r");
return;
case '\"':
sb.append("\\\"");
return;
case '\'':
sb.append("\\\'");
return;
case '\\':
sb.append("\\\\");
return;
}
if (ch > 255) {
sb.append("\\u");
sb.append(numString(ch, 4, 15, 4));
} else if ((ch < ' ') || ((ch > '~') && (ch <= 160))) {
sb.append('\\');
int len;
if ((ch > '~') || ((next >= '0') && (next <= '7')))
len = 3;
else
len = (ch >= 8) ? 2 : 1;
sb.append(numString(ch, 3, 7, len));
} else
sb.append(ch);
}
private static String numString(int val, int shift, int mask, int cnt) {
char[] chars = new char[cnt];
while (--cnt >= 0) {
int d = val & mask;
chars[cnt] = DIGIT[d];
val >>= shift;
}
return new String(chars);
}
private static final char[] DIGIT = {
'0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', 'A', 'B', 'C', 'D', 'E', 'F',
'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V',
'W', 'X', 'Y', 'Z', };
}
//--- end of file ----------------------------------------------------------------------------------
_____________________________________________________________________
java.exe -classpath .\build\classes;.\libs\postgresql.jar de.tangro.sql.IsolationTest
isReadOnly : false
getAutoCommit : false
getCatalog : "dz"
getURL : "jdbc:postgresql://localhost:5432/dz"
getUserName : "dba"
getDriverName : "PostgreSQL Native Driver"
getDriverVersion : "PostgreSQL 7.2 JDBC2"
getDriverMajorVersion : 7
getDriverMinorVersion : 2
getDatabaseProductName : "PostgreSQL"
getDatabaseProductVersion : "7.2.1"
getIdentifierQuoteString : "
getMaxColumnsInTable : 1600
getMaxConnections : 8192
getMaxStatements : 1
getDefaultTransactionIsolation : TRANSACTION_READ_COMMITTED
getTransactionIsolation : TRANSACTION_READ_COMMITTED
insert ta-1 ...
insert ta-1 OK
select ta-2 ...
a
b
select ta-2 OK
select ta-1 ...
select ta-1 OK
update ta-1 ...
update ta-1 OK
Process terminated with exit code 0
_____________________________________________________________________
java.exe -classpath .\build\classes;.\libs\sapdb-jdbc-bin-7.3.00.28a.jar de.tangro.sql.IsolationTest
isReadOnly : false
getAutoCommit : false
getCatalog : null
getURL : "jdbc:sapdb:///TANGRO"
getUserName : "DBA"
getDriverName : "SAP DB"
getDriverVersion : "package com.sap.dbtech.jdbc, SAP DB JDBC Driver, SAP AG, 7.3.0 Build 028-000-086-554"
getDriverMajorVersion : 0
getDriverMinorVersion : 1
getDatabaseProductName : "SAP DB"
getDatabaseProductVersion : "Kernel 7.3.0 Build 020-000-000-000"
getIdentifierQuoteString : "
getMaxColumnsInTable : 254
getMaxConnections : 500
getMaxStatements : 2147483647
getDefaultTransactionIsolation : TRANSACTION_READ_COMMITTED
getTransactionIsolation : TRANSACTION_READ_COMMITTED
insert ta-1 ...
insert ta-1 OK
select ta-2 ...
com.sap.dbtech.jdbc.exceptions.DatabaseException: SAP DBTech SQL: [500] Lock request timeout
at com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:56)
at com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.java:698)
at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:379)
at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:281)
at com.sap.dbtech.jdbc.FetchInfo.executeFetchByAdbs(FetchInfo.java:365)
at com.sap.dbtech.jdbc.ResultSetSapDB.doFetch(ResultSetSapDB.java:298)
at com.sap.dbtech.jdbc.ResultSetSapDB.fetchRelativePacket(ResultSetSapDB.java:605)
at com.sap.dbtech.jdbc.ResultSetSapDB.relative(ResultSetSapDB.java:1642)
at com.sap.dbtech.jdbc.ResultSetSapDB.next(ResultSetSapDB.java:1566)
at de.tangro.sql.IsolationTest.main(IsolationTest.java:71)
Exception in thread "main" Process terminated with exit code 1
_____________________________________________________________________
java.exe -classpath .\build\classes;.\libs\sapdb-jdbc-bin-7.3.00.28a.jar de.tangro.sql.IsolationTest
isReadOnly : false
getAutoCommit : false
getCatalog : null
getURL : "jdbc:sapdb:///TANGRO"
getUserName : "DBA"
getDriverName : "SAP DB"
getDriverVersion : "package com.sap.dbtech.jdbc, SAP DB JDBC Driver, SAP AG, 7.3.0 Build 028-000-086-554"
getDriverMajorVersion : 0
getDriverMinorVersion : 1
getDatabaseProductName : "SAP DB"
getDatabaseProductVersion : "Kernel 7.3.0 Build 020-000-000-000"
getIdentifierQuoteString : "
getMaxColumnsInTable : 254
getMaxConnections : 500
getMaxStatements : 2147483647
getDefaultTransactionIsolation : TRANSACTION_READ_COMMITTED
getTransactionIsolation : TRANSACTION_READ_UNCOMMITTED
insert ta-1 ...
insert ta-1 OK
select ta-2 ...
a
b
c
select ta-2 OK
select ta-1 ...
select ta-1 OK
update ta-1 ...
update ta-1 OK
Process terminated with exit code 0
_____________________________________________________________________
--
_____________________________________________________
| |
| tangro software components gmbh |
| |
| dirk zoettl phone: +49 6221 13336-0 |
| czernyring 22/10 fax: +49 6221 13336-21 |
| d-69115 heidelberg email: [EMAIL PROTECTED] |
| germany internet: www.tangro.de |
|_____________________________________________________|
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
