SQLite के ट्रांज़ैक्शन

• पिछले कुछ वर्षों में SQLite को server process के backend उपयोग के लिए एक शक्तिशाली in-process high-reliability SQL database engine के रूप में खूब सराहना मिली है
• पारंपरिक client या edge application की भूमिका के बजाय ऐसे उपयोग के लिए SQLite developers लगभग सक्रिय रूप से हतोत्साहित करते रहे हैं, फिर भी इसकी लोकप्रियता तेज़ी से बढ़ी है

मुझे SQLite में दिलचस्पी होने के मुख्य कारण:

• वैचारिक रूप से सरल: primary key से partition की गई rows/tuples की B-tree की कल्पना करें। इसे disk पर सुरक्षित रूप से persist करने के लिए व्यापक रूप से test किया गया है, और इसके ऊपर एक SQL interaction layer जोड़ी गई है
• Litestream के ज़रिए एक व्यावहारिक backup strategy बनाई जा सकती है। यह WAL को remote location पर backup करता है और लगातार replicate करता है। backup को एक साधारण command से startup पर अपने-आप restore किया जा सकता है
• मुझे अब भी एक पूरा development environment पसंद है जो offline चल सके
• file::memory: के जरिए in-memory काम संभव है, इसलिए ज़रूरत पड़ने पर test code आसानी से instance शुरू और बंद कर सकता है

Single-writer सीमा

• SQLite developers ने "server पर SQLite की सीमाएँ" अच्छी तरह document की हैं, और best server-side configuration का विश्लेषण किया है। लेकिन सबसे स्पष्ट सीमा high-traffic website है, यानी write-heavy website
• WAL mode में SQLite design के अनुसार single Writer का उपयोग करता है। इसका मतलब है कि एक समय में अधिकतम 1 write transaction और कई read-only transaction की अनुमति है
• यह design high-traffic, write-centric website की bottleneck को उसी single Writer की throughput management पर ले आता है। यह हमें फिर modern technology के एक मुख्य building block तक वापस लाता है

SQLite

• SQLite default रूप से सख्त SERIALIZABLE isolated transaction देता है। यह isolation guarantee का सबसे मज़बूत स्तर है
• single Writer का उपयोग करके SQLite एक तरह का pessimistic concurrency control इस्तेमाल करता है, जिससे यह आसानी से सुनिश्चित कर सकता है कि write transaction के दौरान underlying data बदला नहीं गया है

Postgres

• Postgres वास्तव में SQL standard में परिभाषित SERIALIZABLE default से अलग है, और अधिक ढीला READ COMMITTED चुनता है (काफ़ी अधिक जटिल multiversion concurrency control होने के बावजूद)
  • इस कम सख्ती के कारण non-repeatable reads का जोखिम रहता है। यानी एक ही transaction के भीतर भी, अगर दूसरे COMMITTED transaction द्वारा background में values बदल दी जाएँ, तो एक ही read query को बार-बार चलाने पर अलग परिणाम मिल सकते हैं
  • इस isolation level को चुनकर Postgres यह संभावना खुली छोड़ देता है कि transaction stale data पर काम करे। developers को यह बात ध्यान में रखनी चाहिए
• SERIALIZABLE पर सेट करने पर Postgres optimistic-concurrency control scheme का उपयोग करता है, जिसमें transaction के दौरान access किए गए data को track किया जाता है और commit से पहले जाँचा जाता है कि वह बदला तो नहीं
  • memory usage manage करने के लिए Postgres यह transaction के अनुसार row-level या page-level locks के आधार पर करता है
  • इस pattern को optimistic इसलिए कहा जाता है क्योंकि माना जाता है कि commit के समय transaction द्वारा monitor किया गया underlying data नहीं बदला होगा, और data जितना अधिक fine-grained होगा, उसके बदलने की संभावना उतनी कम होगी

FoundationDB

• transaction सिर्फ relational database तक सीमित नहीं हैं। distributed key-value store में SERIALIZABLE guarantee हासिल करने के लिए optimistic concurrency control का उपयोग किया जाता है
• जब NoSQL पहली बार आया था, तब ACID guarantee वाले distributed NoSQL store आम नहीं थे। FoundationDB ने transaction manifesto लिखकर यह रेखांकित किया कि developers को ACID guarantee से बहुत बड़ा लाभ मिल सकता है
• FoundationDB यह भी सलाह देता है कि optimistic concurrency control के लिए code कैसे और कब लिखा जाए, और यह कि concurrent transaction conflicts के कारण कभी-कभी data बदल जाता है और transaction अपने-आप retry होता है

Idempotence

• idempotent transaction वह transaction है जिसका effect एक बार commit करने और दो बार commit करने पर समान रहता है
• FoundationDB transaction को idempotent बनाने के लिए ऐसे patterns देता है, ताकि conflict के कारण transaction को कई बार retry करना पड़े तो समस्याएँ न हों

तो इन सब बातों को ध्यान में रखते हुए, SQLite कौन-कौन से विकल्प देता है?

BEGIN …

SQLite कई तरीके देता है जिनसे developer engine को बता सकते हैं कि transaction कैसे behave करे, जैसे IMMEDIATE, EXCLUSIVE, DEFERRED keywords; WAL mode में यह व्यवहार मूलतः DEFERRED बनाम IMMEDIATE तक सिमट जाता है

DEFERRED

• transaction READ mode में शुरू होता है, जहाँ वह दूसरे read या write transaction के साथ साथ चल सकता है
• यह केवल तभी blocking READ-WRITE transaction में upgrade होता है जब DB state को बदलने वाली query (INSERT, UPDATE, DELETE) चलाई जाती है
• upgrade के समय अगर DB किसी दूसरे transaction द्वारा locked हो, तो SQLITE_BUSY error लौटती है। client को इसे handle करना पड़ता है

IMMEDIATE

• transaction तुरंत READ-WRITE mode में शुरू होता है
• अगर write transaction पहले से चल रहा हो, तो तुरंत SQLITE_BUSY लौटता है
• इसे कैसे handle करना है, यह client को तय करना पड़ता है

CONCURRENT

• SQLite में एक experimental branch है जो transaction को pessimistic से सीमित optimistic दिशा में ले जाती है
• इसे सीमित इसलिए कहा जाता है क्योंकि optimistic locking row/tuple level पर नहीं, बल्कि DB page level (default 4096 bytes) पर काम करती है
• CONCURRENT mode में SQLite एक साथ कई write transaction active रहने दे सकता है, लेकिन commit से पहले यह जाँचता है कि transaction के दौरान access किए गए pages transaction शुरू होने के बाद से बदले नहीं हैं
• अगर conflict नहीं होता, तो changes क्रमवार commit किए जाते हैं और सख्त SERIALIZABLE guarantee हासिल होती है। conflict होने पर SQLITE_BUSY लौटता है

HC-Tree

• SQLite की एक और experimental branch [HC-Tree] है, जो optimistic row/tuple-level locking देने का लक्ष्य रखने वाला ongoing work है। इसकी एक दिलचस्प उपलब्धि यह है कि यह benchmarks का एक शानदार set देती है, जो ऐसे design के performance लाभ को BEGIN CONCURRENT branch की तुलना में दिखाता है

अगर उनकी benchmarking approach लेकर उसे standard options पर चलाया जाए तो क्या होगा?

बेंचमार्किंग

nUpdate=1, nScan=0

• यह write-only transaction दिखाता है कि IMMEDIATE बनाम DEFERRED का फ़ायदा साफ़ है। locking तुरंत हो जाती है और transaction को upgrading cost का असर नहीं झेलना पड़ता
• CONCURRENT thread count बढ़ने और conflicts बढ़ने के साथ throughput में वृद्धि दिखाता है

nUpdate=10, nScan=0

• जैसा अपेक्षित था, write batching ने 16 threads पर updated rows की संख्या में काफ़ी मदद की। CONCURRENT ~12k/sec से बढ़कर ~19k/sec तक जाता है
• IMMEDIATE बनाम DEFERRED कम महत्वपूर्ण हो जाता है, क्योंकि transaction upgrade की cost की तुलना में updates की अपनी cost अधिक महत्वपूर्ण हो जाती है

nUpdate=1, nScan=10

• यह transaction random read के कारण page-level CONCURRENT locking की कमज़ोरी उजागर करना चाहिए
• यह तुरंत दिखाता है कि update करने वाले transaction के लिए IMMEDIATE का उपयोग करना DEFERRED upgrade cost से ज़्यादा महत्वपूर्ण क्यों है
• CONCURRENT के मामले में ये परिणाम बहुत मज़बूत हैं, क्योंकि underlying conflicts वास्तव में बहुत अधिक नहीं बढ़े

nUpdate=0, nScan=10

• यह read-only batched transaction pessimistic concurrency control का प्रभाव दिखाता है
• यह दिखाता है कि सभी transaction के लिए IMMEDIATE को default क्यों नहीं बनाना चाहिए
• CONCURRENT बनाम IMMEDIATE यह संकेत देता है कि CONCURRENT mode के उपयोग में थोड़ा downside है। "हर स्थिति में performance थोड़ी कम हो जाती है"
  • फिर भी CONCURRENT एक अच्छा default option हो सकता है