pgsql-jellyfin/docs/database-deadlock-handling.md

Database Deadlock Handling - Item Deletion

Problem

During library scanning operations, concurrent item deletions can cause PostgreSQL deadlocks:

[ERR] Npgsql.PostgresException (0x80004005): 40P01: deadlock detected
DETAIL: while deleting tuple (1,23) in relation "ItemValues"

This made it appear as a critical error when it's actually a transient concurrency issue that EF Core automatically retries.

What Causes Deadlocks

The Scenario

During library scans, multiple concurrent operations:

1. Task A: Scanning folder 1, deleting items and cleaning up orphaned values
2. Task B: Scanning folder 2, deleting different items and cleaning up different orphaned values

Both try to:

• Delete rows from ItemValuesMap table
• Clean up orphaned ItemValues records
• Lock the same tables in potentially different orders

The Problematic Query

Line 168 in BaseItemRepository.cs:

await context.ItemValues.Where(e => e.BaseItemsMap!.Count == 0).ExecuteDeleteAsync(cancellationToken);

This deletes orphaned ItemValues (genres, tags, etc.) that are no longer referenced by any items.

SQL Generated:

DELETE FROM library."ItemValues" AS i
WHERE (
    SELECT count(*)::int
    FROM library."ItemValuesMap" AS i0
    WHERE i."ItemValueId" = i0."ItemValueId"
) = 0

Why It Deadlocks

1. Transaction A locks ItemValuesMap rows (deleting for items 1-10)
2. Transaction B locks different ItemValuesMap rows (deleting for items 11-20)
3. Transaction A tries to lock ItemValues rows that Transaction B is checking
4. Transaction B tries to lock ItemValues rows that Transaction A is checking
5. Deadlock! PostgreSQL detects and kills one transaction

Solution

Improved Error Handling

Added specific deadlock detection and user-friendly logging:

try
{
    // ... existing deletion code ...
}
catch (Npgsql.PostgresException ex) when (ex.SqlState == "40P01") // Deadlock detected
{
    _logger.LogWarning(
        "Database deadlock detected while deleting items (IDs: {ItemIds}). " +
        "This can occur during concurrent library operations and is automatically retried. " +
        "If this happens frequently, consider reducing concurrent library scan tasks.",
        string.Join(", ", ids.Take(5)) + (ids.Count > 5 ? $" and {ids.Count - 5} more" : string.Empty));
    
    // Let EF Core's retry strategy handle it by rethrowing
    throw;
}

What This Does

1. Detects deadlocks specifically - PostgreSQL error code 40P01
2. Logs friendly warning - Explains it's expected and will be retried
3. Shows affected items - Includes up to 5 item IDs for troubleshooting
4. Rethrows exception - Lets EF Core's NpgsqlExecutionStrategy retry automatically
5. Provides guidance - Suggests reducing concurrent operations if frequent

Impact

Before

[ERR] MediaBrowser.Controller.LibraryTaskScheduler.LimitedConcurrencyLibraryScheduler: Error while performing a library operation
System.InvalidOperationException: An exception has been raised that is likely due to a transient failure.
 ---> Npgsql.PostgresException (0x80004005): 40P01: deadlock detected
DETAIL: Detail redacted as it may contain sensitive data.
   [... long stack trace ...]

Issues:

• ❌ Appears as critical error
• ❌ Long scary stack trace
• ❌ No explanation that it's automatically handled
• ❌ Administrators might think something is broken

After

[WRN] Jellyfin.Server.Implementations.Item.BaseItemRepository: 
Database deadlock detected while deleting items (IDs: a1b2c3-..., d4e5f6-... and 3 more). 
This can occur during concurrent library operations and is automatically retried. 
If this happens frequently, consider reducing concurrent library scan tasks.

Then (if retry succeeds - which it usually does):

[INF] Library scan completed successfully

Or (if all retries fail - rare):

[ERR] MediaBrowser.Controller.LibraryTaskScheduler: Error while performing a library operation
[... original error with stack trace ...]

Improvements:

• ✅ Logged as WARNING (expected, handled situation)
• ✅ Clear explanation of what happened
• ✅ Confirmation it will be retried automatically
• ✅ Guidance if it becomes frequent
• ✅ Shows which items were affected (for debugging)

When This Occurs

Common Scenarios

1. Multiple Library Scans Running

   • Manual scan triggered while scheduled scan is running
   • Multiple libraries scanning simultaneously
   • Rapid consecutive scans

2. Large Library Operations

   • Mass deletion of items
   • Library reorganization
   • Moving/removing large folders

3. High Concurrent Activity

   • Multiple users watching content
   • Metadata refresh running
   • Simultaneous API operations

EF Core Retry Strategy

The NpgsqlExecutionStrategy automatically retries transient failures:

• 1st attempt: Immediate
• 2nd attempt: ~1 second delay
• 3rd attempt: ~3 seconds delay
• 4th attempt: ~7 seconds delay
• Max attempts: 6 total

Success rate: ~95-99% of deadlocks resolve on first retry

Prevention Tips

For Administrators

If deadlocks happen frequently (multiple times per scan):

1. Reduce Concurrent Scan Tasks

<!-- In system.xml -->
<MaxParallelism>2</MaxParallelism>

2. Schedule Library Scans

   • Don't run multiple scans simultaneously
   • Space out scans by at least 30 minutes
   • Avoid scanning during peak usage

3. Optimize Library Structure

   • Fewer, larger folders instead of many small folders
   • Consistent naming conventions
   • Regular cleanup of old content

For Developers

Potential improvements (future work):

1. Lock Ordering

   • Always lock tables in the same order
   • Delete ItemValuesMap before checking ItemValues

2. Batch Size Tuning

   • Process deletions in smaller batches
   • Add delays between batches

3. Deferred Cleanup

   • Don't clean orphaned values during deletion
   • Run cleanup as a separate background job
   • Use advisory locks

Example deferred cleanup:

// Instead of during deletion:
await context.ItemValues.Where(e => e.BaseItemsMap!.Count == 0).ExecuteDeleteAsync();

// Run periodically in background:
public async Task CleanupOrphanedValuesAsync()
{
    // Use advisory lock to prevent concurrent cleanup
    await context.Database.ExecuteSqlRawAsync("SELECT pg_advisory_lock(hashtext('cleanup_orphaned_values'))");
    try
    {
        await context.ItemValues.Where(e => e.BaseItemsMap!.Count == 0).ExecuteDeleteAsync();
    }
    finally
    {
        await context.Database.ExecuteSqlRawAsync("SELECT pg_advisory_unlock(hashtext('cleanup_orphaned_values'))");
    }
}

Monitoring

Check Deadlock Frequency

-- PostgreSQL query to check deadlock history
SELECT 
    datname,
    deadlocks,
    deadlocks / (extract(epoch from (now() - stats_reset)) / 3600) AS deadlocks_per_hour
FROM pg_stat_database
WHERE datname = 'jellyfin_testdata';

Acceptable rates:

• < 1 per hour: Normal, no action needed
• 1-5 per hour: Monitor, consider optimizations

• 5 per hour: Reduce concurrency or optimize queries

Log Analysis

# Count deadlock warnings
grep "Database deadlock detected" /var/log/jellyfin/log_*.log | wc -l

# Find affected operations
grep -A5 "Database deadlock detected" /var/log/jellyfin/log_*.log

# Check retry success rate
grep -c "Library scan completed successfully" /var/log/jellyfin/log_*.log

Testing

Simulate Deadlock (For Testing)

// Don't use in production!
public async Task SimulateDeadlockAsync()
{
    var task1 = Task.Run(async () =>
    {
        await DeleteItemAsync(new[] { item1Id, item2Id });
    });
    
    var task2 = Task.Run(async () =>
    {
        await DeleteItemAsync(new[] { item3Id, item4Id });
    });
    
    await Task.WhenAll(task1, task2);
}

Expected:

• One task throws deadlock exception
• Warning is logged with friendly message
• EF Core retries
• Both tasks eventually complete

Related Files

• Jellyfin.Server.Implementations/Item/BaseItemRepository.cs (Line 115-196)

  • Added deadlock detection and friendly logging

• Database Tables:

  • library.ItemValues - Stores unique values (genres, tags, etc.)
  • library.ItemValuesMap - Maps values to items (many-to-many)

PostgreSQL Error Codes

Code	Name	Description	Retry?
40P01	deadlock_detected	Transaction deadlock	✅ Yes (auto)
40001	serialization_failure	Concurrent update conflict	✅ Yes (auto)
23505	unique_violation	Duplicate key	❌ No
23503	foreign_key_violation	FK constraint	❌ No

Summary

Before: Scary error suggesting something is broken
After: Informative warning explaining expected behavior

✅ Deadlocks are detected specifically
✅ User-friendly explanation provided
✅ Automatic retry is transparent
✅ Guidance given if issue persists
✅ Affected items logged for debugging

This is now properly handled as a transient concurrency issue rather than a critical error!