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
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Chapter 2. Locking Notes</title>
<link rel="stylesheet" href="gettingStarted.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets V1.73.2" />
<link rel="start" href="index.html" title="Getting Started with the Oracle Berkeley DB SQL APIs" />
<link rel="up" href="index.html" title="Getting Started with the Oracle Berkeley DB SQL APIs" />
<link rel="prev" href="normal-sql.html" title="Differences for Users of other SQL Engines" />
<link rel="next" href="lockhandling.html" title="Lock Handling" />
</head>
<body>
<div xmlns="" class="navheader">
<div class="libver">
<p>Library Version 12.1.6.1</p>
</div>
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 2. Locking Notes</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="normal-sql.html">Prev</a> </td>
<th width="60%" align="center"> </th>
<td width="20%" align="right"> <a accesskey="n" href="lockhandling.html">Next</a></td>
</tr>
</table>
<hr />
</div>
<div class="chapter" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a id="lockingnotes"></a>Chapter 2. Locking Notes</h2>
</div>
</div>
</div>
<div class="toc">
<p>
<b>Table of Contents</b>
</p>
<dl>
<dt>
<span class="sect1">
<a href="lockingnotes.html#dbusage">Internal Database Usage</a>
</span>
</dt>
<dt>
<span class="sect1">
<a href="lockhandling.html">Lock Handling</a>
</span>
</dt>
<dd>
<dl>
<dt>
<span class="sect2">
<a href="lockhandling.html#sqllockmodel">SQLite Lock Usage</a>
</span>
</dt>
<dt>
<span class="sect2">
<a href="lockhandling.html#bdblockusage">Lock Usage with the BDB SQL Interface</a>
</span>
</dt>
</dl>
</dd>
</dl>
</div>
<p>
There are some important performance differences between the
BDB SQL interface and SQLite, especially in a concurrent environment.
This chapter gives you enough information about how the BDB SQL interface
uses its database, as opposed to how SQLite uses its
database, in order for you to understand the difference between
the two interfaces. It then gives you some advice on how to
best approach working with the BDB SQL interface in a multi-threaded
environment.
</p>
<p>
If you are an existing user of SQLite, and you care about
improving your application performance when using the BDB SQL interface in
a concurrent situation, you should read this chapter. Existing
users of Berkeley DB may also find some interesting information in
this chapter, although it is mostly geared towards SQLite
users.
</p>
<div class="sect1" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a id="dbusage"></a>Internal Database Usage</h2>
</div>
</div>
</div>
<p>
The BDB SQL interface and SQLite do different things
when it comes to locking data in their databases. In order
to provide ACID transactions, both products must prevent
concurrent access during write operations. Further, both
products prevent concurrent access by obtaining software
level locks that allow only the current holder of the lock to
perform write access to the locked data.
</p>
<p>
The difference between the two is that when SQLite
requires a lock (such as when a transaction is underway), it
locks the entire database and all tables. (This is known as
<span class="emphasis"><em>database level locking</em></span>.) The BDB SQL interface, on the
other hand, only locks the portion of the table being
operated on within the current transactional context (this is
known as <span class="emphasis"><em>page level locking</em></span>). In most
situations, this allows applications using the BDB SQL interface to
operate concurrently and so have better read/write
throughput than applications using SQLite.
This is because there is less lock contention.
</p>
<p>
By default, one Berkeley DB logical database is created within the
single database file for every SQL table that you create. Within
each such logical database, each table row is represented as a
Berkeley DB key/data pair.
</p>
<p>
This is important because the BDB SQL interface uses Berkeley DB's Transaction
Data Store product. This means that Berkeley DB does not have to
lock an entire database (all the tables within a database
file) when it acquires a lock. Instead, it locks a single
Berkeley DB database page (which usually contains a small sub-set
of rows within a single table).
</p>
<p>
The size of database pages will differ from platform to
platform (you can also manually configure this), but usually
a database page can hold multiple key/data pairs; that is,
multiple rows from a SQL table. Exactly how many table
rows fit on a database page depends on the size of your page
and the size of your table rows.
</p>
<p>
If you have an exceptionally small table, it is possible for
the entire table to fit on a single database page. In this
case, Berkeley DB is in essence forced to serialize access to the
entire table when it requires a lock for it.
</p>
<p>
Note, however, that the case of a single table fitting on a
single database page is very rare, and it in fact represents the
abnormal case. Normally tables span multiple pages and so Berkeley DB
will lock only portions of your tables. This locking behavior is
automatic and transparent to your application.
</p>
</div>
</div>
<div class="navfooter">
<hr />
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="normal-sql.html">Prev</a> </td>
<td width="20%" align="center"> </td>
<td width="40%" align="right"> <a accesskey="n" href="lockhandling.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Differences for Users of other SQL Engines </td>
<td width="20%" align="center">
<a accesskey="h" href="index.html">Home</a>
</td>
<td width="40%" align="right" valign="top"> Lock Handling</td>
</tr>
</table>
</div>
</body>
</html>
|
