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

Ernesto Campohermoso · Accepted Answer · 2011-12-20 03:36:34Z

9

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();
}

answered Dec 20, 2011 at 3:36

Ernesto Campohermoso

7,4411 gold badge42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

James Over a year ago

Is there no way that I can lock it and then leave it locked until I rollback? I need to lock it while executing other java commands, and would prefer not to create a thread here.

James Over a year ago

Fair enough I guess I need to be more liberal when determining whether an answer is an 'answer'

Pintu Over a year ago

Is this applicable in multi-thhreaded environment if each thread get a new connection object and interacts with DB

Ernesto Campohermoso Over a year ago

Since the lock is made in database at table level, if you have other threads accessing with new connections they must wait for table lock release.

Collectives™ on Stack Overflow

Lock table in oracle database using JDBC driver

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related