Audience: Managers/Non-database technical staff/RDBMS (database server) Beginners.

Sometimes an application can misbehave.  It is normal for processes inside an RDBMS system to occasionally get blocked by another process which is currently updating data, and this normally goes unnoticed as it is usually short-lived and clears once the update activity finishes and the locks are released.  However, sometimes an application might crash, or freeze, or encounter some kind of problem which prevents it from committing its transaction. When this happens, its process inside the database server will be in a state where it has taken out exclusive locks against the data but, it *is not doing anything*.  To help understand the problem, the image below (click to enlarge) shows a very simple demonstration of how one transaction can block another process and yet not actually be executing anything.

Users or managers might ask their DBA’s “Why it is blocking other processes if it isn’t executing anything?”  This is where the below demonstration might help, and in short, the answer is that it has already executed something, something that caused it to lock data, but, it has not committed those changes, and unless it does, then all other processes wanting to access that data (unless allowing ‘dirty reads’ (reads of data that may currently be being changed and hence current values may represent incorrect data), will be blocked out.



Leave a Reply

Your email address will not be published. Required fields are marked *