1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite Over a Network, Caveats and Considerations</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<h1 align="center">SQLite Over a Network,<br/>Caveats and Considerations</h1>
<h1>Introduction</h1>
<p>
Users of the SQLite library, particularly application developers,
who want to access a SQLite database
from different systems connected by a network are often
tempted to simply open a <a href="c3ref/sqlite3.html">database connection</a> by specifying
a filename which references a database file somewhere within
a network filesystem. ("remote database" here)
This "file" is then accessed by means of
OS API's which permit the illusion of I/O from/to a local file.
The illusion is good but imperfect in important ways.
</p><p>
This simple, "remote database" approach is usually not the best way
to use a single SQLite database from multiple systems,
(even if it appears to "work"),
as it often leads to various kinds of trouble and grief.
Because these problems are inevitable with some usages,
but not frequent or repeatable,
it behooves application developers to not rely
on early testing success to decide
that their remote database use will work as desired.
</p>
<h1>Issues Arising with Remote Database Files</h1>
<p>
This diagram shows components and their linkages
for reference in the discussion following:
</p>
<div class="center imgcontainer">
<div style="max-width:705px;"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" width="705" height="84" viewBox="0 0 940.32 112.32">
<path d="M2,110L182,110L182,2L2,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="92" y="39" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Client</text>
<text x="92" y="73" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Application</text>
<path d="M380,110L560,110L560,2L380,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="470" y="22" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">SQLite</text>
<text x="470" y="56" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Database</text>
<text x="470" y="90" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Engine</text>
<path d="M758,12L758,99A90 10 0 0 0 938 99L938,12A90 10 0 0 0 758 12A90 10 0 0 0 938 12" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="848" y="47" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Database</text>
<text x="848" y="81" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">File(s)</text>
<polygon points="182,56 193,51 193,60" style="fill:rgb(0,0,0)"/>
<polygon points="380,56 368,60 368,51" style="fill:rgb(0,0,0)"/>
<path d="M187,56L374,56" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="281" y="37" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">SQLite API</text>
<text x="281" y="74" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Calls</text>
<polygon points="560,56 571,51 571,60" style="fill:rgb(0,0,0)"/>
<polygon points="758,56 746,60 746,51" style="fill:rgb(0,0,0)"/>
<path d="M565,56L752,56" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="659" y="37" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">DB Engine</text>
<text x="659" y="74" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">File I/O</text>
</svg>
</div>
</div>
<p>
The issues arise from the properties and utilization
of the two data/control channels between the above three blocks.
</p>
<h2>Channel Traffic Volume</h2>
<p>
The "API Call" channel carries less information
than the "File I/O" channel.
API calls to submit queries or specify data modification
normally require substantially fewer bits to be passed
back and forth than are transferred to/from
the database file to store or find the data.
Query result retrieval will normally require much more file traffic
than API traffic because the data to be returned is rarely
to be found without reading unrequested data.
</p>
<h2>Channel Bandwidth</h2>
<p>
The API Call channel operates at processor main memory speeds
(Giga-words/second), with data often passed by reference (and so not copied.)
In contrast, even the fastest File I/O channels are slower.
They require the data to be copied, usually over a medium
requiring bit-serialization. For spinning magnetic media,
transfers await platter rotation and head movement, then
are limited by spin velocity.
</p>
<p>
When the File I/O channel includes a network connection,
(in addition to some genuine File I/O at its far end),
additional slowness is imposed. Even where raw transfer
rate does not limit bandwidth, the traffic must still be
packetized and buffered at both ends.
Additional layers of I/O handlers add scheduling delays.
However, slowed transfers are the least significant
issue with network filesystems.
</p>
<h2>Channel Reliability</h2>
<p>
The "API Call" channel is highly reliable, to the extent
that error rates are unstated and ignored as negligible.
The channel fails only when the system loses power
(excepting meteorites, etc.)
</p>
<p>
The "File I/O" channel, when it directly reaches a local storage device,
is also highly reliable.
(Spinning storage MTBF exceeds 1 million hours,
and NVRAM lasts longer.)
Local devices also have a characteristic
which is critical for enabling database management software
to be designed to ensure <a href="transactional.html">ACID</a> behavior:
When all process writes to the device have completed,
(when POSIX fsync() or Windows FlushFileBuffers() calls return),
the filesystem then either has
stored the "written" data or will do so
before storing any subsequently written data.
</p>
<p>
When network filesystem apparatus and software layers are interposed
between filesystem clients and a filesystem over an actual storage device,
significant sources of failure and misbehavior are introduced.
While network data transfers are error-checked well, transfer packets
do not all reliably arrive at their destination once sent.
Some packets are clobbered by other packets and must be resent.
Under packet clobbering conditions, repeated retries
can impose delays exceeding
what is needed for similar data to reach local storage.
Some portions of what a client writes can end up stored
out of time order relative to other portions written.
</p>
<p>
Because of the disordering and outright data loss
which occur in network filesystem writes, it is critical
that sets of file writes can be accurately known to be done
before a subsequent set of file writes begins.
This assurance is obtained by use of robustly designed
and correctly implemented fsync() (or equivalent) OS functions.
Unfortunately for some applications, network filesystem sync
operation can be less robust than local filesystem sync.
Attaining robust sync in the face of network packet transport errors
is hard, and safeguards are sometimes relaxed in favor of performance.
</p>
<p>
A similar hazard arises with file locking in network filesystems.
SQLite relies on exclusive locks for write operations, and those have
been known to operate incorrectly for some network filesystems. This
has led to database corruption. That may happen again as the designers
of such change their implementation to suit more common use cases.
</p>
<p>
The bottom line is that network filesystem sync and locking reliability
vary among implementations and installations. The design
assumptions upon which it relies may hold more true where
an application is tested than where it is relied upon.
<b>Rely upon it at your (and your customers') peril.</b>
See <a href="lockingv3.html#how_to_corrupt">How To Corrupt Your Database Files</a>.
</p>
<h1>Performance and Reliability Issues</h1>
<p>
From the above diagram and discussion, it is obvious that
performance (aka "speed") is degraded by insertion
of a network link into one of the two channels.
Consideration of relative traffic volumes between
the API Call channel and the File I/O channel
reveals that such insertion will have less performance
impact at the API Call channel.
</p>
<p>
Consideration of reliability impact is easier, with a clearer outcome:
Inserting a network link into the API Call channel may also result
in call failures at times. But if the Client Application
has bothered to use SQL/SQLite transactions properly,
such failures will only cause a transaction to fail
and be rolled back, without compromising the integrity
of the data. In contrast, if the network link is
inserted into the File I/O channel, transactions may fail
(as for the API Call insertion) but with the additional
effect that the remote database is corrupted.
</p>
<p>
These network unreliability issues can be mitigated,
completely or to an acceptable degree,
by using SQLite in rollback mode.
However, the SQLite library is not tested in across-a-network
scenarios, nor is that reasonably possible.
Hence, use of a remote database is done <b>at the user's risk</b>.
</p>
<h1>Recommendations</h1>
<p>
Generally, if your data is separated from the application
by a network, you want to use a client/server database.
This is due to the fact that the database engine acts
as a bandwidth-reducing filter on the database traffic.
</p><p>
If your data is separated from the application by a network,
you want the low-traffic link to be across the network,
not the high-traffic link. This means that the database engine
needs to be on the same machine as the database itself.
Such is the case with a client/server database like PostgreSQL.
SQLite is different in that the database engine runs on
the same machine as the application, which forces the
higher-traffic link to traverse the network in remote
database scenarios. That normally results in lower performance.
</p><p>
Network filesystems do not support the ability to do
simultaneous reads and writes while at the same time
keeping the database consistent.
So if you have multiple clients on multiple different
machines which need to do simultaneous database
reads and writes, you have these choices:
</p><p>
1. Use a client/server database engine.
<a href=https://postgresql.org/>PostgreSQL</a>
is an excellent choice. A variation of this is:
</p><p>
2. Host an SQLite database in <a href="wal.html">WAL mode</a>, but do
all reads and writes from processes on the same machine
that stores the database file.
Implement a proxy that runs on the database machine that
relays read/write requests from remote machines.
</p><p>
3. Use SQLite in <a href="isolation.html">rollback mode</a>.
This means you can have multiple simultaneous readers or one writer,
but not simultaneous readers and writers.
</p><p>
Application programmers should be cognizant of the possibility
that their application's users will elect to use a remote database
if they can do so. Unless one of the above choices
has been effected, or one at a time, exclusive access is used,
a programmer should consider blocking that
election unless reliability is of little importance.
</p>
<h1>Summary</h1>
<p>
Choose the technology that is right for you and your customers.
If your data lives on a different machine from your application,
then you should consider a client/server database.
SQLite is designed for situations where the data and application
coexist on the same machine.
SQLite can still be made to work in many remote database
situations, but a client/server solution will usually work
better in that scenario.
</p>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/useovernet.in?m=9c50e45c37">2022-06-22 21:14:29</a> UTC </small></i></p>
|
