Robert,

> This may be more appropriate for ib-support but I was wondering how
> one could atomically update a range of records (that need individual
> SQL statements) using JDBC? I've seen the "FOR UPDATE" method, but I
> couldn't find a lot of details on how it is used. I.e. if you
> "don't" update the records how do you release the lock, etc...

In FB 1.0.x (and all IB up to 6.0.x) SELECT ... FOR UPDATE does not
work. Reason for this is multi-generational architecture. In order to
lock records one had to do dummy update:

UPDATE myTable SET someCol = someCol WHERE <myCondition>

Only in this case records are locked. In FB 1.5 Nickolay Samofatov
added syntax SELECT ... FROM ... FOR UPDATE WITH LOCK. This statement
locks records too.

Locks are released on commit/rollback (I think this the case for all
database servers).

> Basically, I have a bunch of scheduled tasks that have an
> "isRunning" column. If there is a server restart (or something like
> that) I want to lock all the records with aborted "isRunning"
> states, update them to false, and then release all the locks. But I
> want to perform the update one at a time instead of a single UPDATE
> WHERE statement. I also want to know that records are already
> locked.
>
> Is there any way to do this sort of multi-user programming in SQL
> without having to resort to adding extra fields to my tables that
> track transaction status?

What is the problem with the following code?

con.setAutoCommit(false);
Statement query = con.createStatement();

// lock all records
query.executeUpdate(
"UPDATE running_table SET isRunning = isRunning " +
"WHERE isRunning = 'T'");

PreparedStatement update = con.prepareStatement(
"UPDATE running_table SET isRunning = 'F' WHERE pkCol = ?");

ResultSet rs = query.executeQuery(
"SELECT pkCol FROM running_table WHERE isRunning = 'T'");

while(rs.next() {
update.setObject(1, rs.getObject(1));
update.executeUpdate();
}

con.commit();

Transaction guarantees you that either all updates will succeed or all
of them will fail.

The only problem I see is that there is no way to find if record is
locked or not. However there is a trick. Firebird has two possible
strategies for the case when it finds the lock conflict: abort
straightaway or wait some time and try again, maybe lock will be
released. In JayBird we use isc_tpb_wait by default. You have to use
isc_tpb_nowait (@see topics where specifying TPB mapping in JDBC URL
is described). In this case any update that you perform will raise you
SQLException with lock conflict error code (you have to check error
code in API Guide or Language Reference). So, you need similar loop:

con.setAutoCommit(false);
Statement query = con.createStatement();
PreparedStatement update = con.prepareStatement(
"UPDATE running_table SET isRunning = isRunning WHERE pkCol = ?");

// you have to select all records where isRunning = 'T'
// because your transaction does not see changes from previous one
// yet. However update will fail anyway.
ResultSet rs = query.executeQuery(
"SELECT pkCol FROM running_table WHERE isRunning = 'T'");

ArrayList pks = new ArrayList();
while(rs.next())
pks.add(rs.getObject(1));

Iterator iter = pks.iterator();
while(iter.hasNext()) {
Object item = iter.netx();
update.setObject(1, item);
try {
update.executeUpdate();
} catch(SQLException ex) {
if (ex.getErrorCode() == LOCK_CONFLICT)
doSomething();
else
throw ex;
} finally {
// release all locks from update statement
con.rollback();
}
}

Note, latter loop has con.rollback() statement right after the update.
This means that all locks are released without causing any harm (not
100% sure, try).

Best regards,
Roman Rokytskyy