Deadlock opening two JDBC transactions in the same thread

Question

I'm testing some database drivers through JDBC. One of the tests consists on testing the transactional capabilities of the db:

1. I open two connections to the same db (with autoCommit=false).
2. On connection A, I insert one row in a table without performing a commit.
3. On connection B, I expect not to see that row yet.
4. On connection A, I perform a commit.
5. On connection B, I expect to see that row.

The test works fine for Oracle 12, but for other databases like HSQLDB, Derby or SQL Server, NO: It blocks in the middle of step 3.

I guess the cause might be something related to transaction isolation parameter, but I've tried all possible values when creating both connections, with the same result always.

Why does it block? And why Oracle does no block?

This is my code:

public class JdbcTest
{
    @Test
    public void twoTransactionsTest()
        throws SQLException,
        IOException
    {
        String tableName="dummy01";
        // 1. I open two connections to the same db (with autoCommit=false).
        try (Connection connectionA=createConnection(); Connection connectionB=createConnection())
        {
            createTable(connectionA, tableName);

            // 2. On connection A, I insert one row in a table without performing a commit.
            execute(connectionA, "INSERT INTO " + tableName + " VALUES(50, 'grecia')");

            // 3. On connection B, I expect not to see that row yet.
            int records=queryAndCountRows(connectionB, "SELECT id FROM " + tableName + " WHERE id=50");
            assertEquals(0, records);

            // 4. On connection A, I perform a commit.
            connectionA.commit();

            // 5. On connection B, I expect to see that row.
            records=queryAndCountRows(connectionB, "SELECT * FROM " + tableName + " WHERE id=50");
            assertEquals(1, records);
            dropTable(connectionA, tableName);
        }
    }

    private Connection createConnection()
        throws SQLException,
        IOException
    {
        String url="jdbc:hsqldb:demo.hsqldb";
        String user="demo";
        String password="";
        Connection connection=DriverManager.getConnection(url, user, password);
        connection.setAutoCommit(false);
        return connection;
    }

    private int queryAndCountRows(Connection connection, String sql)
        throws SQLException
    {
        try (PreparedStatement pst=connection.prepareStatement(sql))
        {
            try (ResultSet rs=pst.executeQuery())
            {
                int records=0;
                while (rs.next())
                {
                    records++;
                }
                return records;
            }
        }
    }

    private void execute(Connection connection, String sql)
        throws SQLException
    {
        try (Statement statement=connection.createStatement())
        {
            statement.execute(sql);
        }
    }

    private void createTable(Connection connection, String tableName)
        throws SQLException
    {
        try
        {
            execute(connection, "DROP TABLE " + tableName);
        }
        catch (SQLException e)
        {
            // If the table already exists, let's ignore this error.
        }
        execute(connection, "CREATE TABLE " + tableName + "(id NUMBER(5) NOT NULL, name VARCHAR2(100))");
    }

    private void dropTable(Connection connection, String tableName)
        throws SQLException
    {
        execute(connection, "DROP TABLE " + tableName);
    }
}

My dependencies:

<dependency>
  <groupId>org.hsqldb</groupId>
  <artifactId>hsqldb</artifactId>
  <version>2.3.2</version>
</dependency>
<dependency>
  <groupId>com.oracle</groupId>
  <artifactId>ojdbc6</artifactId>
  <version>11.2.0</version>
</dependency>
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>6.1.0.jre8</version>
</dependency>

Thanks in advance.

Answer 1

The isolation is implemented differently in those DB systems, please follow this link for more information:

http://www.dba-in-exile.com/2012/11/isolation-levels-in-oracle-vs-sql-server.html

In short, Oracle implements it in a way that writer do not block readers, but the same is not true for other RDBMS.