Metadata merge operations fail with 'Lock wait timeout exceeded' on large batches — long-running transaction anti-pattern

[bannert1337]

Description

When merging metadata values (authors, categories, languages, etc.) that affect a large number of books, the operation fails with a database lock timeout. This happens because the entire merge is processed inside a single @Transactional method that holds row locks on book_metadata and book_file for the entire duration.

Steps to Reproduce

1. Have a library with many books sharing the same metadata value (e.g., 443 books with language "German", 26 with "de", 25 with "deu", 3 with "ger")
2. Go to Metadata Manager → Languages
3. Select all 4 variants and merge into "German"
4. Operation fails after ~50 seconds

Expected Behavior

The merge should succeed regardless of batch size. Large merges should be processed in smaller sub-transactions to avoid holding database locks for extended periods.

Actual Behavior

org.hibernate.exception.LockTimeoutException: could not execute batch
[(conn=20067) Lock wait timeout exceeded; try restarting transaction]
[update book_file set added_on=?, archive_type=?, bitrate=?, book_id=?,
 book_type=?, channels=?, chapter_count=?, chapters_json=?, codec=?,
 current_hash=?, description=?, duration_seconds=?, file_name=?, file_size_kb=?,
 file_sub_path=?, is_folder_based=?, initial_hash=?, is_book=?, is_fixed_layout=?,
 sample_rate=? where id=?]

Root Cause

In MetadataManagementService.consolidateLanguages() (and similar methods for authors, categories, etc.):

@Transactional
public void consolidateMetadata(MergeMetadataType metadataType, ...) {
    // ...
    consolidateLanguages(targetValues, valuesToMerge, moveFile);
}

private void consolidateLanguages(List<String> targetValues, List<String> valuesToMerge, boolean moveFile) {
    for (String oldLanguage : valuesToMerge) {
        List<BookMetadataEntity> booksWithOldLanguage = bookMetadataRepository.findAllByLanguageIgnoreCase(oldLanguage);
        // 443 + 26 + 25 + 3 = 497 books loaded into memory
        
        bookMetadataRepository.saveAll(booksWithOldLanguage);  // Queued but not flushed
        writeMetadataToFile(booksWithOldLanguage, moveFile);   // Writes to 497 files on disk
        // Each file write calls bookRepository.saveAndFlush(book) which updates book_file.current_hash
        // All held in the SAME transaction with row locks on book_metadata and book_file
    }
}

The @Transactional on consolidateMetadata() means all 497 books are processed in one giant transaction. File I/O (writing metadata to PDF/CBZ files) is slow, so the transaction holds InnoDB row locks for minutes, exceeding innodb_lock_wait_timeout (default 50s).

Environment

• Grimmory version: v3.0.3
• Database: MariaDB (default innodb_lock_wait_timeout = 50)
• Batch size: 497 books (language merge)

Suggested Fix

Option 1 — Batch in smaller chunks with intermediate commits:

private void consolidateLanguages(...) {
    for (String oldLanguage : valuesToMerge) {
        List<BookMetadataEntity> books = bookMetadataRepository.findAllByLanguageIgnoreCase(oldLanguage);
        // Process in batches of 50
        for (List<BookMetadataEntity> batch : Lists.partition(books, 50)) {
            transactionTemplate.executeWithoutResult(status -> {
                bookMetadataRepository.saveAll(batch);
                writeMetadataToFile(batch, moveFile);
            });
        }
    }
}

Option 2 — Use Propagation.REQUIRES_NEW in writeMetadataToFile() so each book commits independently:

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void writeMetadataToFile(BookMetadataEntity metadata, boolean moveFile) {
    // ... process single book
}

Option 3 — Decouple file writing from DB transaction:

• Update DB metadata first (fast, within transaction)
• Queue file writes to a background task (slow, outside transaction)

Related Issues

• Metadata Manager crashes with "An unexpected error occurred" during background file moves — race condition in writeMetadataToFile() #1162 — Race condition between FileMoveService and MetadataManagementService causing file-not-found crashes during metadata operations

Both issues stem from the same architectural problem: metadata operations do too much work (file I/O + DB updates) inside a single transaction.

[coderabbitai[bot]]

🔗 Related PRs

#234 - fix(api): skip physical books in metadata manager to prevent NPE [merged]
#258 - fix(metadata): preserve lock states and fallback values in metadata refresh [merged]
#287 - fix(api): merge processFile() transactions with calling transactions [merged]
#560 - fix(metadata): update book retrieval methods to fix lazy init exception when removing some authors/retrieving metadata from file [merged]
#916 - fix(regenerate-covers): wrap book query stream in transaction to prevent lazy init exception [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[imnotjames]

Duplicate of #818