At 07:18 PM 26-09-01 -0700, David Husarik wrote:

>Hi Group,
>
>I have a Delphi 4.03 Professional application running
>on 2 machines of 3 machine peer-to-peer network. The
>database server is the third machine. This is an
>automated process that drives diskette duplicators
>that read files submitted on diskette. I received the
>following error message from the Delphi program.
>
>W2Reader Error
>ISC ERROR CODE: 335544345
>ISC ERROR MESSAGE:
>Lock conflict on no wait transaction deadlock.
>
>I know that the conflict arose when both machines hit
>the same point in the program. This is when a number
>must be obtained to rename the file from the diskette
>so the file can be transfered to the hard drive for
>further processing.

Correct assumption. <g>

>I have a table in the database
>that contains 1 record with 3 fields, ID, Next_Write,
>Next_Write2. Mch 1 reads and updates Next_Write. Mch
>2 reads and updates Next_Write2.

[snip]

>The Question is how do I handle this error.

The answer is - avoid this sort of bottleneck altogether. In a multi-user system you should avoid this type of serialization unless there is an absolute (read "legal" or "life-threatening") requirement for it. If possible, get your number from a generator because generators (unlike absolutely positively everything else) are independent of transactions. Once a transaction gets that number, no other transaction can get it; and there is no possibility of any locking conflict.

You are getting this locking bottleneck because the main task and the getting and setting of the serial number are enclosed in the same transaction context. You will get a deadlock (or, more descriptively, a "livelock" which locks up all users of the number table without resolution) as soon as any two transactions both want to get and set the number.

If you *must* implement this serialization like this, do it with a stored procedure that returns the new number as an output parameter. Make a function in your app that calls the procedure to get and update the number table in a __separate__, dedicated, pessimisically locked transaction that just "hits and runs". Make both this transaction and your main transaction tiReadCommitted. Set PessimisticLocking to true on the get-and-set function.

Make the function deliver the procedure call statement via a TIB_DSQL (Execute, not Open, natch!!). Set its StoredProcHasDML property true. Read the Fields[0] property of the statement in its AfterExecute event to get the number. Have the function handle any locking conflict by rolling back and resubmitting until you get a result.

Dragging this even further, if you have to have an *unbroken* sequence (hard work!!) have a look at the Tech Info sheet on the subject, downloadable from the TechInfo page of the IBO website.

>Do I need
>to set other properties to resolve the conflict like
>LockWait to true or Isolation to something else. Or
>should I put the program into repeat ... until loop
>until I get the value of Next_Write or Next_Write2
>depending upon the machine.

Theoretically you could handle it this way. In practice, it will create an avoidable bottleneck. The important thing here is to separate the get-and-set work on the number table from the main task.

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________