Lock table in oracle database using JDBC driver

Question

I am trying to lock a table in my testing framework in order to trigger timeouts. I am using the following code to lock the table.

String lock = "lock table "+ tableName +" in exclusive mode";
try {
        connection = DriverManager.getConnection(_url, _username, _password);
        connection.setAutoCommit(false);  
        Statement stmt1=connection.createStatement();  
        stmt1.executeUpdate(lock);  

    } catch (SQLException e) {
        e.printStackTrace();
    }

After I have executed this I try to access the page and add an element to the account. However it doesn't work, the lock doesn't seem to have occurred. Any idea why this wouldn't work? I am currently testing this in java and once I have executed that lock I am sleeping the thread while manually testing the page, could this be causing problems?

Thanks, James

Answer 1

The oracle's documentation say:

You use the LOCK TABLE statement to lock entire database tables in a specified lock mode so that you can share or deny access to them.. Row share locks allow concurrent access to a table; they prevent other users from locking the entire table for exclusive use. Table locks are released when your transaction issues a commit or rollback.

So you need to start a transaction and maintain it. The following code maintains the table locked for one minute:

String lock = "lock table "+ tableName +" in exclusive mode";
try {
    connection = DriverManager.getConnection(_url, _username, _password);
    connection.setAutoCommit(false);  
    Statement stmt1=connection.createStatement();  
    stmt1.execute(lock);  
    int i = 0;
    while (i<60) {            
        Thread.sleep(1000);    //Sleep one second
        i++;                   
    }
    connection.commit();

} catch (SQLException e) {
    connection.rollback();
    e.printStackTrace();
}