Java – Oracle database SELECT… FOR UPDATE with autocommit on

javajdbcoracleoracle11g

I'm running this query against an Oracle database (11g). The connection has the default value for autocommit which is 'true'.

Connection con = driver.connect(url, properties);
String query = "SELECT EMPID, NAME FROM EMPLOYEE FOR UPDATE";

Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = statement.executeQuery(query);

SELECT… FOR UPDATE states that it "lock the selected rows so that other users cannot lock or update the rows until you end your transaction." With autocommit on it should be immediately after the query is executed. But the lock is present till the connection is closed.

(I checked this by running query select * FROM DBA_DML_LOCKS WHERE NAME = 'EMPLOYEE'; on sqlplus.)

Is this a bug or can somebody please explain why?
Thank you in advance.

Best Answer

As said in documentation:

the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed

Another documentation:

Enabling auto-commit may be more convenient, but gives you less control. For example, you have no option to roll back changes. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.

So for this query type you should use manual commit

Related Solutions

Java – JDBC Lock a row using SELECT FOR UPDATE, doesn’t work

You are super-confused, but at least things look better after your edits. There are multiple ways to do this, but the best way I've found is to actually use JDBC's ResultSet.update* methods:

First, you need to prepare your SELECT ... FOR UPDATE statement with the ResultSet.CONCUR_UPDATABLE argument, like this:

ps = conn.prepareStatement(query,
                           ResultSet.TYPE_FORWARD_ONLY,
                           ResultSet.CONCUR_UPDATABLE);

Then, you have to actually update the table using the ResultSet:

if(rs.next())
{
    rs.updateString(columnIndex, "new_hostname");
    rs.updateRow();
}

Third, you probably need to use a transaction, which I can see in your update. Hopefully, your DbUtil.close methods won't throw any exceptions, check for null, etc. Also, if your method gets any more complicated, you should have rollback logic in there, too.

You should not have to modify my.ini for any reason.

Sql – Oracle select for update behaviour

The behaviour you've encountered for FOR UPDATE SKIP LOCKED has been described in this blog note. My understanding is that the FOR UPDATE clause is evaluated AFTER the WHERE clause. The SKIP LOCKED is like an additional filter that guarantees that among the rows that would have been returned, none are locked.

Your statement is logically equivalent to: find the first row from card_numbers and return it if it is not locked. Obviously this is not what you want.

Here is a little test case that reproduces the behaviour you describe:

SQL> CREATE TABLE t (ID PRIMARY KEY)
  2  AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;

Table created

SESSION1> select id from t where rownum <= 1 for update skip locked;

        ID
----------
         1

SESSION2> select id from t where rownum <= 1 for update skip locked;

        ID
----------

No row is returned from the second select. You can use a cursor to work around this issue:

SQL> CREATE FUNCTION get_and_lock RETURN NUMBER IS
  2     CURSOR c IS SELECT ID FROM t FOR UPDATE SKIP LOCKED;
  3     l_id NUMBER;
  4  BEGIN
  5     OPEN c;
  6     FETCH c INTO l_id;
  7     CLOSE c;
  8     RETURN l_id;
  9  END;
 10  /

Function created

SESSION1> variable x number;
SESSION1> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
1

SESSION2> variable x number;
SESSION2> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
2

Since I've explicitely fetched the cursor, only one row will be returned (and only one row will be locked).

Best Answer

Related Solutions

Java – JDBC Lock a row using SELECT FOR UPDATE, doesn’t work

Sql – Oracle select for update behaviour

Related Topic