Deadlock
When two programs each wait for data the other has locked, so neither can continue; DB2 cancels one of them.
Also written timeout, lock timeout, SQLCODE -911, SQLCODE -913, -911, locking
DB2 locks data while a program changes it, so that two programs cannot change the same rows at once. Usually a program that needs locked data simply waits until the lock is released. Two problems can happen.
A timeout is when a program waits longer than the system allows. A deadlock is when two programs each hold a lock the other needs, so both would wait forever. DB2 notices a deadlock and picks one program to fail, so the other can continue.
The failing program gets SQLCODE -911 or -913. With -911, DB2 has already rolled back the program's changes since its last commit. With -913, it has not, and the program must decide whether to roll back or try again. The reason code in the message says whether it was a timeout or a deadlock.
These errors are most common when batch jobs and online transactions use the same tables at the same time. The usual fixes are to commit more often, so locks are held for less time, and to make programs access tables in the same order.
Where can I learn this hands-on?
Right here, at ZedBank. You take a junior job at a simulated bank and learn each part of the work by doing it, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- SQLCODEThe result code DB2 returns after every SQL statement, saying whether it worked and what happened if it did not.
- Commit and rollbackMaking a set of changes permanent together, or undoing them together, so a transaction never half-happens.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- CursorA mechanism for stepping through a set of rows one at a time, when a query returns more than one result.
- Batch windowThe period, usually overnight, in which batch work has to finish before the business needs the systems back.