How is possible to get "Stat 78" within Begin transaction OP 1419?

A deadlock, also known as "deadly embrace", can occur with two threads
executing at the same time, with slightly different code. For example,
consider an ATM program, transferring money from one account to
another. Assume that the SAVINGS database contains savings accounts,
and CHECKING database contains checking accounts.

Now, watch what happens when you execute transfers (in opposite
directions) on the same accounts:

User A User B
========= =========
Begin Transaction
Begin Transaction
Read SAVINGS Record
Read CHECKING Record
Update SAVINGS Record
(Obtains an implicit lock on the SAVINGS record)
Update CHECKING Record
(Obtains implicit lock on CHECKING record)
Update CHECKING Record
(Process goes into a WAIT STATE, since the Checking Record is already
locked by another user...)
Update SAVINGS Record
(Process goes into a WAIT STATE, since the Checking Record is
already locked by
another user...)

The transaction for User A has the SAVINGS record locked and is now
waiting for the CHECKING record and won't do anything until it gets it.

The transaction for User B has the CHECKING record locked and is now
waiting for the SAVINGS record and won't do anything until it gets it.

The database engine detects this and kicks out one or both with a
Status 78 to prevent an infinite wait state.


The problem is actually bad code design. If the code were rewritten to
ALWAYS lock the CHECKING record FIRST, then it can be avoided. Of
course, you can still cause the same problem with a three-way deadlock
if you're not careful. Alternatively, if you lock the CUSTOMER record
first, you can also guarantee that the CHECKING and SAVINGS tables
don't conflict. You just keep going up the data tree until you find a
common record that will keep ALL other users out...
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/2007 ***



ddion02 (AT) videotron (DOT) ca wrote:

Thanks Bill,
I understand the issue. But I use the "Begin transact" with
operation code 1419. (No wait). Then I'm not suppose to have stat 78.
I can have a deadlock but by my own manage (See Below). I had also
have Stat 78 with Insert operation. ???

BB=write by Bill Bach
DD=write by ddion

User A User B
========= =========
BB Begin Transaction
BB Begin Transaction
BB Read SAVINGS Record
BB Read CHECKING Record
BB Update SAVINGS Record
BB (Obtains an implicit lock on the SAVINGS record)
BB Update CHECKING Record
BB (Obtains implicit lock on CHECKING
record)
DD Read CHECKING Record
DD (Optains status 84 and I manage this status with "WHILE not TIME-
OUT retry")
DD If TIME-OUT exit application with status 84
BB Update CHECKING Record
BB (Process goes into a WAIT STATE, since the Checking Record is
already
BB locked by another user...)
DD Read SAVING Record
DD (Optains status 84 and I manage this
status with "WHILE not TIME-OUT retry")
DD If TIME-OUT exit application with
status 84

BB Update SAVINGS Record
BB (Process goes into a WAIT STATE, since
the Checking Record is
BB already locked by
BB another user...)
BB

Be sure that you are actually calling the AbortTransaction operation
before exiting your app. Failure to do so could be part of it and
could be a problem after a timeout.

You may also wish to add a +500 to the BeginTransaction operation
(1919) to have the MKDE *not* retry certain operations.

Deadlocks on Inserts are indeed possible, since the database must lock
Index Pages as well as the data pages. In fact, index pages are much
more likely to conflict. This is why having SMALL page sizes with
fewer indices is better for transactional programming.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/2007 ***


ddion02 (AT) videotron (DOT) ca wrote: