SQLite concurrency और इसे ध्यान में रखना क्यों ज़रूरी है

 (jellyfin.org)
11 पॉइंट द्वारा GN⁺ 2025-11-02 | 2 टिप्पणियां | WhatsApp पर शेयर करें
  • SQLite की file-based संरचना सरल है, लेकिन एक ही समय में कई write operations होने पर locking conflicts हो सकते हैं
  • Jellyfin लंबे समय से SQLite का उपयोग करता रहा है, लेकिन कुछ सिस्टमों में transaction के दौरान database locked errors के कारण application रुक जाने की समस्या आई
  • EF Core की interceptor सुविधा का उपयोग करके तीन locking strategies (No-Lock, Optimistic, Pessimistic) लागू की गईं, ताकि इस समस्या को कम किया जा सके
  • Optimistic तरीका retry-आधारित है और performance loss को न्यूनतम रखता है, जबकि Pessimistic तरीका स्थिरता बढ़ाता है लेकिन speed कम कर देता है
  • यह approach दूसरे EF Core applications पर भी आसानी से लागू की जा सकने वाली संरचना देती है, और SQLite concurrency समस्याओं के लिए एक व्यावहारिक विकल्प प्रदान करती है

SQLite की मूल संरचना और सीमाएँ

  • SQLite एक file-based relational database engine है जो application के अंदर चलता है
    • यह सारा data एक ही file में store करता है और किसी अलग server application की ज़रूरत नहीं होती
  • क्योंकि application इस single file को पूरी तरह manage करता है, इसलिए एक साथ कई processes access करें तो conflict का जोखिम रहता है
  • इसलिए SQLite का उपयोग करने वाले applications को एक समय में केवल एक write operation करना चाहिए

Write-Ahead-Log(WAL) mode

  • SQLite, WAL(Write-Ahead-Log) फीचर के ज़रिए concurrency limitations को कम करता है
    • WAL file, database changes को रिकॉर्ड करने वाली journal file की तरह काम करती है
    • यह कई write operations को parallel queue कर सकती है और read के समय WAL के changes लागू करती है
  • लेकिन WAL भी पूरी तरह परफेक्ट नहीं है, और कुछ परिस्थितियों में locking conflicts अब भी होते हैं

SQLite transaction problems

  • Transactions, change operations की atomicity सुनिश्चित करने और read blocking को नियंत्रित करने का काम करते हैं
  • Jellyfin के कुछ सिस्टमों में transaction के दौरान SQLite “database is locked” error लौटाकर तुरंत रुक जाता था
    • यह समस्या operating system, disk speed, या virtualization की मौजूदगी से अलग रिपोर्ट की गई
    • इसे reproduce करना कठिन था और यह अनियमित रूप से होती थी, इसलिए root cause पता लगाना मुश्किल था

Jellyfin में SQLite का उपयोग और समस्याएँ

  • Recommended environment (non-network storage, SSD) में समस्या कम दिखती थी, लेकिन 10.11 से पहले के versions में parallel jobs limit bug के कारण
    • library scan jobs बहुत ज़्यादा parallel में चलने लगे, जिससे हज़ारों concurrent write requests पैदा हुए
    • SQLite engine की retry और timeout limits पार हो गईं, और database overload व errors होने लगे
  • लंबे transactions और inefficient queries ने भी समस्या को और बढ़ाया

EF Core आधारित समाधान

  • Jellyfin ने codebase को EF Core पर migrate करने के बाद बेहतर structural control हासिल किया
  • EF Core के Interceptors का उपयोग करके सभी commands और transactions की execution intercept कर transparent locking control लागू किया गया
  • तीन locking strategies लागू की गईं
    1. No-Lock: default mode, कोई अलग locking नहीं। ज़्यादातर मामलों में performance impact से बचने के लिए उपयोग
    2. Optimistic Locking: failure होने पर Polly library की मदद से retry
    3. Pessimistic Locking: हर write operation से पहले ReaderWriterLockSlim के ज़रिए पूरे database को lock

Optimistic Locking कैसे काम करता है

  • इसमें माना जाता है कि operation सफल होगा, और failure पर retry किया जाता है
    • अगर दो write operations टकराते हैं, तो एक fail होकर कुछ समय बाद फिर retry करता है
  • Polly library का उपयोग करके केवल locking के कारण हुई failures को ही retry target बनाया गया
  • Pessimistic तरीके की तुलना में overhead कम है और performance loss भी कम है

Pessimistic Locking कैसे काम करता है

  • हर write operation के समय पूरा database lock कर दिया जाता है
    • write के दौरान सभी read और write operations block हो जाते हैं
  • यह तरीका सबसे स्थिर है, लेकिन सबसे धीमा भी
    • उदाहरण के लिए, “Alice” table पढ़ते समय “Bob” table में write technically संभव हो, तब भी इसे अनुमति नहीं दी जाती
  • ReaderWriterLockSlim का उपयोग करके multiple reads की अनुमति और केवल single write की अनुमति दी जाती है

आगे की योजना: Smart Locking

  • Optimistic और Pessimistic तरीकों को मिलाकर Smart Locking लाने पर विचार हो रहा है
    • उद्देश्य दोनों के फ़ायदे मिलाकर performance और stability के बीच संतुलन बनाना है

नतीजे और लागू करने की संभावना

  • शुरुआती test results के अनुसार, दोनों locking modes समस्या हल करने में प्रभावी रहे
  • समस्या की मूल वजह अब भी स्पष्ट नहीं है, लेकिन अब users के पास Jellyfin को स्थिर रूप से उपयोग करने का विकल्प है
  • इंटरनेट पर भी ऐसी कई error reports थीं, लेकिन कोई पूरी तरह स्थापित समाधान मौजूद नहीं था
  • Jellyfin का implementation EF Core interceptor-आधारित, आसानी से copy और apply किया जा सकने वाला ढाँचा देता है
    • caller को अंदरूनी locking behavior के बारे में जानने की ज़रूरत नहीं होती
  • वही SQLite concurrency समस्या झेल रहे दूसरे EF Core applications भी इसे तुरंत उपयोग में ला सकते हैं

संबंधित पढ़ाई

2 टिप्पणियां

 
GN⁺ 2025-11-02
Hacker News राय

  • पहले मैंने SQLite की blocking समस्या झेली थी, और उसकी वजह डिस्क fragmentation थी
    पुराने Android टैबलेट पर वे उपयोगकर्ता, जो ऐप को कई वर्षों तक रोज़ 8 घंटे इस्तेमाल करते थे, धीमापन और lock errors की शिकायत कर रहे थे
    डेटा कॉपी करके लेने पर समस्या दोबारा सामने नहीं आती थी, लेकिन आखिर में डिवाइस को सीधे लेकर जांच करने पर पता चला कि DB फ़ाइल को नई जगह कॉपी करके फिर मूल नाम से वापस रखकर उसे 'defrag' करने पर समस्या पूरी तरह गायब हो गई
    Jellyfin DB में भी इसी तरीके से performance improvement मिला

    • यह fragmentation से ज़्यादा flash memory degradation होने की संभावना लगती है। क्या वह eMMC storage वाला सस्ता टैबलेट था?
    • क्या SQLite के VACUUM function से भी यही असर मिल सकता है?
    • यह दिलचस्प मामला है। लेकिन उपयोगकर्ता से सीधे defrag नहीं कराया जा सकता, इसलिए कोई व्यावहारिक समाधान चाहिए

  • SQLite के transactions डिफ़ॉल्ट रूप से "deferred" mode में शुरू होते हैं
    यानी असली write operation की कोशिश करने तक यह write lock नहीं लेता
    SQLITE_BUSY error तब आता है जब read transaction write में बदलने की कोशिश करे और उसी समय कोई दूसरा transaction पहले से write lock पकड़े हो
    समाधान है busy_timeout सेट करना, और जिन transactions में write शामिल हो वे "immediate" mode में शुरू करना
    इसका अच्छा विवरण इस blog post में है

    • मैंने भी शुरुआत में सोचा था कि यह SQLITE_BUSY समस्या है। ऐसे मामलों को मैंने यहाँ इकट्ठा किया है
    • मुझे SQLITE_BUSY एक तरह की architecture smell लगती है। WAL mode में मैं read-only connection pool और single write connection pool को अलग डिज़ाइन करता हूँ। इससे lock holding state साफ़ रहती है और contention situation को पहले से डिज़ाइन में शामिल किया जा सकता है
    • इस मामले में busy_timeout लागू नहीं होता। WAL mode में pages एक ही log file में append होते हैं, इसलिए अगर read के दौरान write में बदलने की कोशिश हो तो serialization guarantee के लिए SQLite तुरंत fail कर देता है। "immediate" mode इससे बचाता है
    • आखिरकार SQLite इस्तेमाल करने वाला कोई भी व्यक्ति कभी न कभी इस समस्या से जलता है और कारण खोजने में समय लगाता है
    • blog post में SQLITE_BUSY का ज़िक्र नहीं था, शायद configuration छूट गई थी

  • लगता है लेख का कुछ हिस्सा गलत है
    SQLite खुद lock management करता है, इसलिए application को सीधे file access control करने की ज़रूरत नहीं होती
    और WAL कई parallel writes की अनुमति नहीं देता। यह सिर्फ read और single write को एक साथ होने देता है

    • मुझे भी SQLite बहुत पसंद है, लेकिन यह लेख बुनियादी concurrency concepts से ही गलत है, इसलिए मैं इसे recommend नहीं कर सकता

  • SQLite एक शानदार database है, लेकिन इसके default settings बहुत ज़्यादा conservative हैं, यह खटकता है
    production में इस्तेमाल करने के लिए कई PRAGMA settings समायोजित करनी पड़ती हैं

    • जानना चाहूँगा कि कौन-से PRAGMA डिफ़ॉल्ट रूप से on रखना अच्छे होंगे
    • ऐसी स्थिति में तो शायद fork करके नए defaults बनाना बेहतर होगा

  • जब SQLite का नया hctree फीचर stable हो जाएगा, तब से मैं शायद सिर्फ SQLite ही इस्तेमाल करूँगा
    नाम में hc शायद High Concurrency का संक्षेप होगा
    official docs link

  • ऐसे लेख पढ़कर लगता है कि समस्या के root cause analysis की बजाय बस अस्थायी उपाय बताए गए हैं
    गहरी debugging और शोध के ज़रिए सटीक कारण निकालना ही सच में मूल्यवान साझा होगा

    • शायद लेखक ने केवल आंशिक जांच की और अपूर्ण समाधान साझा किया। संभव है कि HN पर बेहतर जवाब निकलवाने का यही इरादा रहा हो। जैसे कहा जाता है, “गलत जवाब पोस्ट करो, सही जवाब जल्दी मिल जाएगा”

  • लगता है यह भी ठीक से नहीं समझा गया कि WAL mode भी आखिरकार single write, multiple read संरचना ही है
    parallel writes संभव नहीं हैं, यह सिर्फ इतना करता है कि read transactions writes से block न हों
    पूरा MVCC होता तो अच्छा होता, लेकिन मौजूदा संरचना भी सिद्धांत समझ लेने पर काफ़ी अच्छी तरह काम करती है

  • मैंने भी Jellyfin में ऐसी ही समस्या झेली है
    आम तौर पर सब ठीक चलता है, लेकिन कुछ स्थितियों में DB lock होकर अटक जाता है
    logs में सिर्फ “database is locked” दिखता है, और आखिर में Docker container restart करना पड़ता है
    यह ज़्यादातर TV UI में कई बटन तेज़ी से दबाने पर होता है

  • थोड़ा अलग विषय है, लेकिन अगर SQLite in-memory DB को बहुत सारे insert/delete कामों में इस्तेमाल करें तो memory usage लगातार बढ़ता जाता है
    उदाहरण के लिए, हर 5 मिनट में 1 लाख rows डालकर हटाने का काम कई दिनों तक दोहराएँ तो macOS पर memory 1GB तक पहुँच जाती है
    ऐसे मामले में कौन-सी setting समायोजित की जा सकती है, यह जानना चाहूँगा

    • क्या आप समय-समय पर VACUUM चला रहे हैं, और auto_vacuum on है या नहीं, यह देखना चाहिए
      VACUUM docs
    • यह buffer का उपयोग पैटर्न के हिसाब से dynamically adjust होना भी सामान्य व्यवहार हो सकता है
    • अगर हर बार सारी rows हटानी हैं, तो बस table को drop करके फिर create करना ज़्यादा efficient होगा

  • SQLite शानदार है, लेकिन ऐसी समस्याएँ देखकर कभी-कभी लगता है कि सीधे Postgres इस्तेमाल करना बेहतर होगा
    अगर single-file portability या embedded use case नहीं है, तो Postgres concurrency समस्याएँ ज़्यादा आसानी से संभालता है

    • लेकिन Jellyfin एक self-hosted media server है, इसलिए अगर Postgres अनिवार्य हो जाए तो installation और maintenance जटिल हो जाएंगे। SQLite ज़्यादा उपयुक्त है
    • Jellyfin ज़्यादातर घरेलू single-user environment में चलता है, इसलिए SQLite काफ़ी है। बस मौजूदा settings शायद optimal नहीं हैं
    • SQLite के फायदों को नज़रअंदाज़ करके Postgres पर जाने की बात करना ऐसा है जैसे “camping करने जाओ और कहा जाए कि एक cabin बना लो”
    • Postgres के साथ installation ही नहीं, version upgrade के समय migration का भी ध्यान रखना पड़ता है। SQLite में यह बोझ नहीं है
    • Jellyfin ने हाल ही में DB code को Entity Framework में rewrite किया है, इसलिए आगे चलकर DB selection को ज़्यादा flexible बनाने की तैयारी हो रही है
 
ndrgrd 2025-11-03

"हैं?" वाला हिस्सा था, इसलिए मैंने तुरंत पहले comments देखे, और जैसा उम्मीद थी...