Worker processes can acquire only shared locks, but they can still be involved in deadlocks with processes that acquire exclusive locks. The locks they hold meet one or more of these conditions:
A coordinating process holds a table lock as part of a parallel query.
The coordinating process could hold exclusive locks on other tables as part of a previous query in a transaction.
A parallel query is running at transaction isolation level 3 or using holdlock and holds locks.
A parallel query is joining two or more tables while another process is performing a sequence of updates to the same tables within a transaction.
A single worker process can be involved in a deadlock such as those between two serial processes. For example, a worker process that is performing a join between two tables can deadlock with a serial process that is updating the same two tables.
In some cases, deadlocks between serial processes and families involve a level of indirection.
For example, if a task holds an exclusive lock on tableA and needs a lock on tableB, but a worker process holds a family-duration lock on tableB, the task must wait until the transaction that the worker process is involved in completes.
If another worker process in the same family needs a lock on tableA, the result is a deadlock. Figure 12-1 illustrates the following deadlock scenario:
The family identified by fid 8 is doing a parallel query that involves a join of stock_tbl and sales_tbl, at transaction level 3.
The serial task identified by spid 17 (T17) is performing inserts to stock_tbl and sales_tbl in a transaction.
These are the steps that lead to the deadlock:
W8 9, a worker process with a fid of 8 and a spid of 9, holds a shared lock on page 10862 of stock_tbl.
T17 holds an exclusive lock on page 634 of sales_tbl. T17 needs an exclusive lock on page 10862, which it cannot acquire until W8 9 releases its shared lock.
The worker process W8 10 needs a shared lock on page 634, which it cannot acquire until T17 releases its exclusive lock.
Figure 12-1: A deadlock involving a family of worker processes
