Hi Yvon,

Why would you want to use this cautious locking scenario? These days,
applications tend to use an optimistic locking scenario.

The latter one does not lock at all, but detects cross-updates using
an extended WHERE clause. The WHERE clause contains the primary key
AND checks whether column values have changed after the row was
retrieved from the database, i.e. by another user's update. I always
add a column Row_Version to each table. The Row_Version value is
incremented each time the row is updated (either from app code, but
preferrably by trigger code).

My update statement would read:
UPDATE <table> SET ...
WHERE KeyColumn = :KeyColumn
AND Row_Version = :Row_Version /* value previously fetched for row*/

You're using JDBC from JBuilder? The db-access components in JBuilder
are designed to handle the optimistic locking scenario. Try using the
Resolver and look for a property "UpdateMode" or a similar name.

Good luck,
Marco

PS. I see you are located in the Netherlands. I can provide more
support & consultancy if you wish.

-------------------------------------
-- Marco Bommeljé
-- Bommeljé Crompvoets en partners bv
-- Demkaweg 11 JE
-- 3555 HW Utrecht
-- Tfn: (030) 24 28 369
-- Fax: (030) 24 28 368
-- Elm: mbommelj@...
-------------------------------------


firebirdyah wrote:

> Hello,
>
> I would like to access one database from different Java programs. I
> am using JDBC. See the following simple scenario, program A and B
> (they need to do a lot more in the transaction):
>
> A: auto commit is set to false (implicit transaction start)
> A: update table XYZ
> B: auto commit is set to false (implicit transaction start)
> B: update table XYZ: this actions is hold until A commits
> A: commit
> B: an exception is generated on the update:
> interbase.interclient.DeadlockException: [interclient][interbase]
> deadlock.
>
> I need to prevent the deadlock exception. Using isql, I can do this
> by reserving a table by staring a transaction: "set transaction read
> write wait reserving XYZ for protected write;", no deadlock occurs,
> the 'B' programs wait as required.
>
> This can not be done in JDBC, while it (as far as I can see) starts a
> transaction automatically. When I try con.commit(); statement.execute
> ("set transaction"); I get the exception [interclient][interbase]
> invalid transaction handle (expecting explicit transaction start).
>
> My question is: how can I update the same table from different Java
> programs without getting an deadlock exception?
>
> Where can I find more info about the possible SQL-statements I can do
> in JDBC? Examples?
>
> Kind regards, Yvon
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/>.