-
Notifications
You must be signed in to change notification settings - Fork 1.2k
/
advanced.html
1909 lines (1823 loc) · 85.7 KB
/
advanced.html
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
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Copyright 2004-2022 H2 Group. Multiple-Licensed under the MPL 2.0,
and the EPL 1.0 (https://h2database.com/html/license.html).
Initial Developer: H2 Group
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>
Advanced
</title>
<link rel="stylesheet" type="text/css" href="stylesheet.css" />
<!-- [search] { -->
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="frameMe();">
<table class="content"><tr class="content"><td class="content"><div class="contentDiv">
<!-- } -->
<h1>Advanced</h1>
<a href="#result_sets">
Result Sets</a><br />
<a href="#large_objects">
Large Objects</a><br />
<a href="#linked_tables">
Linked Tables</a><br />
<a href="#spatial_features">
Spatial Features</a><br />
<a href="#recursive_queries">
Recursive Queries</a><br />
<a href="#updatable_views">
Updatable Views</a><br />
<a href="#transaction_isolation">
Transaction Isolation</a><br />
<a href="#mvcc">
Multi-Version Concurrency Control (MVCC)</a><br />
<a href="#clustering">
Clustering / High Availability</a><br />
<a href="#two_phase_commit">
Two Phase Commit</a><br />
<a href="#compatibility">
Compatibility</a><br />
<a href="#keywords">
Keywords / Reserved Words</a><br />
<a href="#standards_compliance">
Standards Compliance</a><br />
<a href="#windows_service">
Run as Windows Service</a><br />
<a href="#odbc_driver">
ODBC Driver</a><br />
<a href="#acid">
ACID</a><br />
<a href="#durability_problems">
Durability Problems</a><br />
<a href="#using_recover_tool">
Using the Recover Tool</a><br />
<a href="#file_locking_protocols">
File Locking Protocols</a><br />
<a href="#passwords">
Using Passwords</a><br />
<a href="#password_hash">
Password Hash</a><br />
<a href="#sql_injection">
Protection against SQL Injection</a><br />
<a href="#remote_access">
Protection against Remote Access</a><br />
<a href="#restricting_classes">
Restricting Class Loading and Usage</a><br />
<a href="#security_protocols">
Security Protocols</a><br />
<a href="#tls_connections">
TLS Connections</a><br />
<a href="#uuid">
Universally Unique Identifiers (UUID)</a><br />
<a href="#system_properties">
Settings Read from System Properties</a><br />
<a href="#server_bind_address">
Setting the Server Bind Address</a><br />
<a href="#file_system">
Pluggable File System</a><br />
<a href="#file_system_split">
Split File System</a><br />
<a href="#java_objects_serialization">
Java Objects Serialization</a><br />
<a href="#limits_limitations">
Limits and Limitations</a><br />
<a href="#glossary_links">
Glossary and Links</a><br />
<h2 id="result_sets">Result Sets</h2>
<h3>Statements that Return a Result Set</h3>
<p>
The following statements return a result set: <code>SELECT</code>, <code>TABLE</code>, <code>VALUES</code>,
<code>EXPLAIN</code>, <code>CALL</code>, <code>SCRIPT</code>, <code>SHOW</code>, <code>HELP</code>.
<code>EXECUTE</code> may return either a result set or an update count.
Result of a <code>WITH</code> statement depends on inner command.
All other statements return an update count.
</p>
<h3>Limiting the Number of Rows</h3>
<p>
Before the result is returned to the application, all rows are read by the database.
Server side cursors are not supported currently.
If only the first few rows are interesting for the application, then the
result set size should be limited to improve the performance.
This can be done using <code>FETCH</code> in a query
(example: <code>SELECT * FROM TEST FETCH FIRST 100 ROWS ONLY</code>),
or by using <code>Statement.setMaxRows(max)</code>.
</p>
<h3>Large Result Sets and External Sorting</h3>
<p>
For large result set, the result is buffered to disk. The threshold can be defined using the statement
<code>SET MAX_MEMORY_ROWS</code>.
If <code>ORDER BY</code> is used, the sorting is done using an
external sort algorithm.
In this case, each block of rows is sorted using quick sort, then written to disk;
when reading the data, the blocks are merged together.
</p>
<h2 id="large_objects">Large Objects</h2>
<h3>Storing and Reading Large Objects</h3>
<p>
If it is possible that the objects don't fit into memory, then the data type
CLOB (for textual data) or BLOB (for binary data) should be used.
For these data types, the objects are not fully read into memory, by using streams.
To store a BLOB, use <code>PreparedStatement.setBinaryStream</code>. To store a CLOB, use
<code>PreparedStatement.setCharacterStream</code>. To read a BLOB, use <code>ResultSet.getBinaryStream</code>,
and to read a CLOB, use <code>ResultSet.getCharacterStream</code>.
When using the client/server mode, large BLOB and CLOB data is stored in a temporary file
on the client side.
</p>
<h3>When to use CLOB/BLOB</h3>
<p>
By default, this database stores large LOB (CLOB and BLOB) objects separate from the main table data.
Small LOB objects are stored in-place, the threshold can be set using
<a href="commands.html#set_max_length_inplace_lob" class="notranslate" >MAX_LENGTH_INPLACE_LOB</a>,
but there is still an overhead to use CLOB/BLOB. Because of this, BLOB and CLOB
should never be used for columns with a maximum size below about 200 bytes.
The best threshold depends on the use case; reading in-place objects is faster
than reading from separate files, but slows down the performance of operations
that don't involve this column.
</p>
<h2 id="linked_tables">Linked Tables</h2>
<p>
This database supports linked tables, which means tables that don't exist in the current database but
are just links to another database. To create such a link, use the
<code>CREATE LINKED TABLE</code> statement:
</p>
<pre>
CREATE LINKED TABLE LINK('org.postgresql.Driver', 'jdbc:postgresql:test', 'sa', 'sa', 'TEST');
</pre>
<p>
You can then access the table in the usual way.
Whenever the linked table is accessed, the database issues specific queries over JDBC.
Using the example above, if you issue the query <code>SELECT * FROM LINK WHERE ID=1</code>,
then the following query is run against the PostgreSQL database: <code>SELECT * FROM TEST WHERE ID=?</code>.
The same happens for insert and update statements.
Only simple statements are executed against the target database, that means no joins
(queries that contain joins are converted to simple queries).
Prepared statements are used where possible.
</p>
<p>
To view the statements that are executed against the target table, set the trace level to 3.
</p>
<p>
If multiple linked tables point to the same database (using the same database URL), the connection
is shared. To disable this, set the system property <code>h2.shareLinkedConnections=false</code>.
</p>
<p>
The statement <a href="commands.html#create_linked_table" class="notranslate" >CREATE LINKED TABLE</a>
supports an optional schema name parameter.
</p>
<p>
The following are not supported because they may result in a deadlock:
creating a linked table to the same database,
and creating a linked table to another database using the server mode if the other database is open in the same server
(use the embedded mode instead).
</p>
<p>
Data types that are not supported in H2 are also not supported for linked tables,
for example unsigned data types if the value is outside the range of the signed type.
In such cases, the columns needs to be cast to a supported type.
</p>
<h2 id="updatable_views">Updatable Views</h2>
<p>
By default, views are not updatable.
To make a view updatable, use an "instead of" trigger as follows:
</p>
<pre>
CREATE TRIGGER TRIGGER_NAME
INSTEAD OF INSERT, UPDATE, DELETE
ON VIEW_NAME
FOR EACH ROW CALL "com.acme.TriggerClassName";
</pre>
<p>
Update the base table(s) within the trigger as required.
For details, see the sample application <code>org.h2.samples.UpdatableView</code>.
</p>
<h2 id="transaction_isolation">Transaction Isolation</h2>
<p>
Please note that most data definition language (DDL) statements,
such as "create table", commit the current transaction.
See the <a href="commands.html">Commands</a> for details.
</p>
<p>
Transaction isolation is provided for all data manipulation language (DML) statements.
</p>
<p>
H2 supports read uncommitted, read committed, repeatable read, snapshot,
and serializable (partially, see below) isolation levels:
</p>
<ul>
<li><b>Read uncommitted</b><br />
Dirty reads, non-repeatable reads, and phantom reads are possible.
To enable, execute the SQL statement
<code>SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</code>
</li>
<li><b>Read committed</b><br />
This is the default level.
Dirty reads aren't possible; non-repeatable reads and phantom reads are possible.
To enable, execute the SQL statement
<code>SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED</code>
</li>
<li><b>Repeatable read</b><br />
Dirty reads and non-repeatable reads aren't possible, phantom reads are possible.
To enable, execute the SQL statement
<code>SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ</code>
</li>
<li><b>Snapshot</b><br />
Dirty reads, non-repeatable reads, and phantom reads aren't possible.
This isolation level is very expensive in databases with many tables.
To enable, execute the SQL statement
<code>SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SNAPSHOT</code>
</li>
<li><b>Serializable</b><br />
Dirty reads, non-repeatable reads, and phantom reads aren't possible.
Note that this isolation level in H2 currently doesn't ensure equivalence of concurrent and serializable execution
of transactions that perform write operations.
This isolation level is very expensive in databases with many tables.
To enable, execute the SQL statement
<code>SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE</code>
</li>
</ul>
<ul>
<li><b>Dirty reads</b><br />
Means a connection can read uncommitted changes made by another connection.<br />
Possible with: read uncommitted.
</li><li><b>Non-repeatable reads</b><br />
A connection reads a row, another connection changes a row and commits,
and the first connection re-reads the same row and gets the new result.<br />
Possible with: read uncommitted, read committed.
</li><li><b>Phantom reads</b><br />
A connection reads a set of rows using a condition, another connection
inserts a row that falls in this condition and commits, then the first connection
re-reads using the same condition and gets the new row.<br />
Possible with: read uncommitted, read committed, repeatable read.
</li>
</ul>
<h3 id="mvcc">Multi-Version Concurrency Control (MVCC)</h3>
<p>
Insert and update operations only issue a shared lock on the table.
An exclusive lock is still used when adding or removing columns or when dropping the table.
Connections only 'see' committed data, and own changes. That means, if connection A updates
a row but doesn't commit this change yet, connection B will see the old value.
Only when the change is committed, the new value is visible by other connections
(read committed). If multiple connections concurrently try to lock or update the same row, the
database waits until it can apply the change, but at most until the lock timeout expires.
</p>
<h3>Lock Timeout</h3>
<p>
If a connection cannot get a lock on an object, the connection waits for some amount
of time (the lock timeout). During this time, hopefully the connection holding the
lock commits and it is then possible to get the lock. If this is not possible because
the other connection does not release the lock for some time, the unsuccessful
connection will get a lock timeout exception. The lock timeout can be set individually
for each connection.
</p>
<h2 id="clustering">Clustering / High Availability</h2>
<p>
This database supports a simple clustering / high availability mechanism. The architecture is:
two database servers run on two different computers, and on both computers is a copy of the
same database. If both servers run, each database operation is executed on both computers.
If one server fails (power, hardware or network failure), the other server can still continue to work.
From this point on, the operations will be executed only on one server until the other server
is back up.
</p><p>
Clustering can only be used in the server mode (the embedded mode does not support clustering).
The cluster can be re-created using the <code>CreateCluster</code> tool without stopping
the remaining server. Applications that are still connected are automatically disconnected,
however when appending <code>;AUTO_RECONNECT=TRUE</code>, they will recover from that.
</p><p>
To initialize the cluster, use the following steps:
</p>
<ul>
<li>Create a database
</li><li>Use the <code>CreateCluster</code> tool to copy the database to
another location and initialize the clustering.
Afterwards, you have two databases containing the same data.
</li><li>Start two servers (one for each copy of the database)
</li><li>You are now ready to connect to the databases with the client application(s)
</li></ul>
<h3>Using the CreateCluster Tool</h3>
<p>
To understand how clustering works, please try out the following example.
In this example, the two databases reside on the same computer, but usually, the
databases will be on different servers.
</p>
<ul>
<li>Create two directories: <code>server1, server2</code>.
Each directory will simulate a directory on a computer.
</li><li>Start a TCP server pointing to the first directory.
You can do this using the command line:
<pre>
java org.h2.tools.Server
-tcp -tcpPort 9101
-baseDir server1
</pre>
</li><li>Start a second TCP server pointing to the second directory.
This will simulate a server running on a second (redundant) computer.
You can do this using the command line:
<pre>
java org.h2.tools.Server
-tcp -tcpPort 9102
-baseDir server2
</pre>
</li><li>Use the <code>CreateCluster</code> tool to initialize clustering.
This will automatically create a new, empty database if it does not exist.
Run the tool on the command line:
<pre>
java org.h2.tools.CreateCluster
-urlSource jdbc:h2:tcp://localhost:9101/~/test
-urlTarget jdbc:h2:tcp://localhost:9102/~/test
-user sa
-serverList localhost:9101,localhost:9102
</pre>
</li><li>You can now connect to the databases using
an application or the H2 Console using the JDBC URL
<code>jdbc:h2:tcp://localhost:9101,localhost:9102/~/test</code>
</li><li>If you stop a server (by killing the process),
you will notice that the other machine continues to work,
and therefore the database is still accessible.
</li><li>To restore the cluster, you first need to delete the
database that failed, then restart the server that was stopped,
and re-run the <code>CreateCluster</code> tool.
</li></ul>
<h3>Detect Which Cluster Instances are Running</h3>
<p>
To find out which cluster nodes are currently running, execute the following SQL statement:
</p>
<pre>
SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'CLUSTER'
</pre>
<p>
If the result is <code>''</code> (two single quotes), then the cluster mode is disabled. Otherwise, the list of
servers is returned, enclosed in single quote. Example: <code>'server1:9191,server2:9191'</code>.
</p>
<p>
It is also possible to get the list of servers by using Connection.getClientInfo().
</p>
<p>
The property list returned from <code>getClientInfo()</code> contains a <code>numServers</code> property that returns the
number of servers that are in the connection list. To get the actual servers, <code>getClientInfo()</code> also has
properties <code>server0</code>..<code>serverX</code>, where serverX is the number of servers minus 1.
</p>
<p>
Example: To get the 2nd server in the connection list one uses <code>getClientInfo('server1')</code>.
<b>Note:</b> The <code>serverX</code> property only returns IP addresses and ports and not hostnames.
</p>
<h3>Clustering Algorithm and Limitations</h3>
<p>
Read-only queries are only executed against the first cluster node, but all other statements are
executed against all nodes. There is currently no load balancing made to avoid problems with
transactions. The following functions may yield different results on different cluster nodes and must be
executed with care: <code>UUID(), RANDOM_UUID(), SECURE_RAND(), SESSION_ID(),
MEMORY_FREE(), MEMORY_USED(), CSVREAD(), CSVWRITE(), RAND()</code> [when not using a seed].
Those functions should not be used directly in modifying statements
(for example <code>INSERT, UPDATE, MERGE</code>). However, they can be used
in read-only statements and the result can then be used for modifying statements.
Identity columns aren't supported.
Instead, sequence values need to be manually requested and then used to insert data (using two statements).
</p>
<p>
When using the cluster modes, result sets are read fully in memory by the client, so that
there is no problem if the server dies that executed the query. Result sets must fit in memory
on the client side.
</p>
<p>
The SQL statement <code>SET AUTOCOMMIT FALSE</code> is not supported in the cluster mode.
To disable autocommit, the method <code>Connection.setAutoCommit(false)</code> needs to be called.
</p>
<p>
It is possible that a transaction from one connection overtakes a transaction from a different connection.
Depending on the operations, this might result in different results, for example when
conditionally incrementing a value in a row.
</p>
<h2 id="two_phase_commit">Two Phase Commit</h2>
<p>
The two phase commit protocol is supported. 2-phase-commit works as follows:
</p>
<ul>
<li>Autocommit needs to be switched off
</li><li>A transaction is started, for example by inserting a row
</li><li>The transaction is marked 'prepared' by executing the SQL statement
<code>PREPARE COMMIT transactionName</code>
</li><li>The transaction can now be committed or rolled back
</li><li>If a problem occurs before the transaction was successfully committed or rolled back
(for example because a network problem occurred), the transaction is in the state 'in-doubt'
</li><li>When re-connecting to the database, the in-doubt transactions can be listed
with <code>SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT</code>
</li><li>Each transaction in this list must now be committed or rolled back by executing
<code>COMMIT TRANSACTION transactionName</code> or
<code>ROLLBACK TRANSACTION transactionName</code>
</li><li>The database needs to be closed and re-opened to apply the changes
</li></ul>
<h2 id="compatibility">Compatibility</h2>
<p>
This database is (up to a certain point) compatible to other databases such as HSQLDB, MySQL and PostgreSQL.
There are certain areas where H2 is incompatible.
</p>
<h3>Transaction Commit when Autocommit is On</h3>
<p>
At this time, this database engine commits a transaction (if autocommit is switched on) just before returning the result.
For a query, this means the transaction is committed even before the application scans through the result set, and before the result set is closed.
Other database engines may commit the transaction in this case when the result set is closed.
</p>
<h2 id="keywords">Keywords / Reserved Words</h2>
<p>
There is a list of keywords that can't be used as identifiers (table names, column names and so on),
unless they are quoted (surrounded with double quotes).
The following tokens are keywords in H2:
</p>
<table class="main">
<thead>
<tr>
<th rowspan="2">Keyword</th>
<th rowspan="2">H2</th>
<th colspan="6" style="text-align: center">SQL Standard</th>
</tr>
<tr>
<th>2016</th>
<th>2011</th>
<th>2008</th>
<th>2003</th>
<th>1999</th>
<th>92</th>
</tr>
</thead>
<tbody>
<tr><td>ALL</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>AND</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>ANY</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>ARRAY</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td></tr>
<tr><td>AS</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>ASYMMETRIC</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>NR</td><td></td></tr>
<tr><td>AUTHORIZATION</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>BETWEEN</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>NR</td><td>+</td></tr>
<tr><td>BOTH</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CASE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CAST</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CHECK</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CONSTRAINT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CROSS</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CURRENT_CATALOG</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td><td></td></tr>
<tr><td>CURRENT_DATE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CURRENT_PATH</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td></tr>
<tr><td>CURRENT_ROLE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td></tr>
<tr><td>CURRENT_SCHEMA</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td><td></td></tr>
<tr><td>CURRENT_TIME</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CURRENT_TIMESTAMP</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>CURRENT_USER</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>DAY</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>DEFAULT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>DISTINCT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>ELSE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>END</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>EXCEPT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>EXISTS</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>NR</td><td>+</td></tr>
<tr><td>FALSE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>FETCH</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>FILTER</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td></tr>
<tr><td>FOR</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>FOREIGN</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>FROM</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>FULL</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>GROUP</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>GROUPS</td>
<td>CS</td><td>+</td><td>+</td><td></td><td></td><td></td><td></td></tr>
<tr><td>HAVING</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>HOUR</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>IF</td>
<td>+</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>ILIKE</td>
<td>CS</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>IN</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>INNER</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>INTERSECT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>INTERVAL</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>IS</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>JOIN</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>KEY</td>
<td>+</td><td>NR</td><td>NR</td><td>NR</td><td>NR</td><td>+</td><td>+</td></tr>
<tr><td>LEADING</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>LEFT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>LIKE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>LIMIT</td>
<td>MS</td><td></td><td></td><td></td><td></td><td>+</td><td></td></tr>
<tr><td>LOCALTIME</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td></tr>
<tr><td>LOCALTIMESTAMP</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td></tr>
<tr><td>MINUS</td>
<td>MS</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>MINUTE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>MONTH</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>NATURAL</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>NOT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>NULL</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>OFFSET</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td><td></td></tr>
<tr><td>ON</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>OR</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>ORDER</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>OVER</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td></tr>
<tr><td>PARTITION</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td></tr>
<tr><td>PRIMARY</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>QUALIFY</td>
<td>+</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>RANGE</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td></tr>
<tr><td>REGEXP</td>
<td>CS</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>RIGHT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>ROW</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td></tr>
<tr><td>ROWNUM</td>
<td>+</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>ROWS</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>SECOND</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>SELECT</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>SESSION_USER</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td></tr>
<tr><td>SET</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>SOME</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>SYMMETRIC</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>NR</td><td></td></tr>
<tr><td>SYSTEM_USER</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>TABLE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>TO</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>TOP</td>
<td>MS<br/>CS</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td>TRAILING</td>
<td>CS</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>TRUE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>UESCAPE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td></tr>
<tr><td>UNION</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>UNIQUE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>UNKNOWN</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>USER</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>USING</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>VALUE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>VALUES</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>WHEN</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>WHERE</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>WINDOW</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td></td><td></td></tr>
<tr><td>WITH</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>YEAR</td>
<td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td><td>+</td></tr>
<tr><td>_ROWID_</td>
<td>+</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
</tbody>
</table>
<p>
Mode-sensitive keywords (MS) are keywords only in some compatibility modes.
</p>
<ul><li>LIMIT is a keywords only in Regular, Legacy, DB2, HSQLDB, MariaDB, MySQL, and PostgreSQL compatibility modes.
It is an identifier in Strict, Derby, MSSQLServer, and Oracle compatibility modes.
</li><li>MINUS is a keyword only in Regular, Legacy, DB2, HSQLDB, and Oracle compatibility modes.
It is an identifier in Strict, Derby, MSSQLServer, MariaDB, MySQL, and PostgreSQL compatibility modes.
</li><li>TOP is a context-sensitive keyword (can be either keyword or identifier)
only in Regular, Legacy, HSQLDB, and MSSQLServer compatibility modes.
It is an identifier unconditionally in Strict, Derby, DB2, MariaDB, MySQL, Oracle, and PostgreSQL compatibility modes.
</li></ul>
<p>
Context-sensitive keywords (CS) can be used as identifiers in some places,
but cannot be used as identifiers in others.
Normal keywords (+) are always treated as keywords.
</p>
<p>
Most keywords in H2 are also reserved (+) or non-reserved (NR) words in the SQL Standard.
Newer versions of H2 may have more keywords than older ones.
Reserved words from the SQL Standard are potential candidates for keywords in future versions.
</p>
<p>There is a compatibility setting
<a href="commands.html#set_non_keywords"><code>SET NON_KEYWORDS</code></a>
that can be used as a temporary workaround for applications that use keywords as unquoted identifiers.</p>
<h2 id="standards_compliance">Standards Compliance</h2>
<p>
This database tries to be as much standard compliant as possible. For the SQL language, ANSI/ISO is the main
standard. There are several versions that refer to the release date: SQL-92, SQL:1999, and SQL:2003.
Unfortunately, the standard documentation is not freely available. Another problem is that important features
are not standardized. Whenever this is the case, this database tries to be compatible to other databases.
</p>
<h3>Supported Character Sets, Character Encoding, and Unicode</h3>
<p>
H2 internally uses Unicode, and supports all character encoding systems and character sets supported by the virtual machine you use.
</p>
<h2 id="windows_service">Run as Windows Service</h2>
<p>
Using a native wrapper / adapter, Java applications can be run as a Windows Service.
There are various tools available to do that. The Java Service Wrapper from
<a href="https://wrapper.tanukisoftware.org">Tanuki Software, Inc.</a>
is included in the installation. Batch files are provided to install, start, stop and uninstall the
H2 Database Engine Service. This service contains the TCP Server and the H2 Console web application.
The batch files are located in the directory <code>h2/service</code>.
</p>
<p>
The service wrapper bundled with H2 is a 32-bit version.
To use a 64-bit version of Windows (x64), you need to use a 64-bit version of the wrapper,
for example the one from
<a href="https://www.krenger.ch/blog/java-service-wrapper-3-5-14-for-windows-x64/">
Simon Krenger</a>.
</p>
<p>
When running the database as a service, absolute path should be used.
Using <code>~</code> in the database URL is problematic in this case,
because it means to use the home directory of the current user.
The service might run without or with the wrong user, so that
the database files might end up in an unexpected place.
</p>
<h3>Install the Service</h3>
<p>
The service needs to be registered as a Windows Service first.
To do that, double click on <code>1_install_service.bat</code>.
If successful, a command prompt window will pop up and disappear immediately. If not, a message will appear.
</p>
<h3>Start the Service</h3>
<p>
You can start the H2 Database Engine Service using the service manager of Windows,
or by double clicking on <code>2_start_service.bat</code>.
Please note that the batch file does not print an error message if the service is not installed.
</p>
<h3>Connect to the H2 Console</h3>
<p>
After installing and starting the service, you can connect to the H2 Console application using a browser.
Double clicking on <code>3_start_browser.bat</code> to do that. The
default port (8082) is hard coded in the batch file.
</p>
<h3>Stop the Service</h3>
<p>
To stop the service, double click on <code>4_stop_service.bat</code>.
Please note that the batch file does not print an error message if the service is not installed or started.
</p>
<h3>Uninstall the Service</h3>
<p>
To uninstall the service, double click on <code>5_uninstall_service.bat</code>.
If successful, a command prompt window will pop up and disappear immediately. If not, a message will appear.
</p>
<h3>Additional JDBC drivers</h3>
<p>
To use other databases (for example MySQL), the location of the JDBC drivers of those databases need to be
added to the environment variables <code>H2DRIVERS</code> or <code>CLASSPATH</code> before
installing the service. Multiple drivers can be set; each entry needs to be separated with a <code>;</code>
(Windows) or <code>:</code> (other operating systems). Spaces in the path names are supported.
The settings must not be quoted.
</p>
<h2 id="odbc_driver">ODBC Driver</h2>
<p>
This database does not come with its own ODBC driver at this time,
but it supports the PostgreSQL network protocol.
Therefore, the PostgreSQL ODBC driver can be used.
Support for the PostgreSQL network protocol is quite new and should be viewed
as experimental. It should not be used for production applications.
</p>
<p>
To use the PostgreSQL ODBC driver on 64 bit versions of Windows,
first run <code>c:/windows/syswow64/odbcad32.exe</code>.
At this point you set up your DSN just like you would on any other system.
See also:
<a href="https://www.postgresql.org/message-id/dg76q0$khn$1@sea.gmane.org">Re: ODBC Driver on Windows 64 bit</a>
</p>
<h3>ODBC Installation</h3>
<p>
First, the ODBC driver must be installed.
Any recent PostgreSQL ODBC driver should work, however version 8.2 (<code>psqlodbc-08_02*</code>) or newer is recommended.
The Windows version of the PostgreSQL ODBC driver is available at
<a href="https://www.postgresql.org/ftp/odbc/versions/msi/">https://www.postgresql.org/ftp/odbc/versions/msi/</a>.
</p>
<h3>Starting the Server</h3>
<p>
After installing the ODBC driver, start the H2 Server using the command line:
</p>
<pre>
java -cp h2*.jar org.h2.tools.Server
</pre>
<p>
The PG Server (PG for PostgreSQL protocol) is started as well.
By default, databases are stored in the current working directory where the server is started.
Use <code>-baseDir</code> to save databases in another directory, for example the user home directory:
</p>
<pre>
java -cp h2*.jar org.h2.tools.Server -baseDir ~
</pre>
<p>
The PG server can be started and stopped from within a Java application as follows:
</p>
<pre>
Server server = Server.createPgServer("-baseDir", "~");
server.start();
...
server.stop();
</pre>
<p>
By default, only connections from localhost are allowed. To allow remote connections, use
<code>-pgAllowOthers</code> when starting the server.
</p>
<p>
To map an ODBC database name to a different JDBC database name,
use the option <code>-key</code> when starting the server.
Please note only one mapping is allowed. The following will map the ODBC database named
<code>TEST</code> to the database URL <code>jdbc:h2:~/data/test;cipher=aes</code>:
</p>
<pre>
java org.h2.tools.Server -pg -key TEST "~/data/test;cipher=aes"
</pre>
<h3>ODBC Configuration</h3>
<p>
After installing the driver, a new Data Source must be added. In Windows,
run <code>odbcad32.exe</code> to open the Data Source Administrator. Then click on 'Add...'
and select the PostgreSQL Unicode driver. Then click 'Finish'.
You will be able to change the connection properties.
The property column represents the property key in the <code>odbc.ini</code> file
(which may be different from the GUI).
</p>
<table class="main">
<tr><th>Property</th><th>Example</th><th>Remarks</th></tr>
<tr><td>Data Source</td><td>H2 Test</td><td>The name of the ODBC Data Source</td></tr>
<tr><td>Database</td><td>~/test;ifexists=true</td>
<td>
The database name. This can include connections settings.
By default, the database is stored in the current working directory
where the Server is started except when the -baseDir setting is used.
The name must be at least 3 characters.
</td></tr>
<tr><td>Servername</td><td>localhost</td><td>The server name or IP address.<br />By default, only remote connections are allowed</td></tr>
<tr><td>Username</td><td>sa</td><td>The database user name.</td></tr>
<tr><td>SSL</td><td>false (disabled)</td><td>At this time, SSL is not supported.</td></tr>
<tr><td>Port</td><td>5435</td><td>The port where the PG Server is listening.</td></tr>
<tr><td>Password</td><td>sa</td><td>The database password.</td></tr>
</table>
<p>
To improve performance, please enable 'server side prepare' under Options / Datasource / Page 2 / Server side prepare.
</p>
<p>
Afterwards, you may use this data source.
</p>
<h3>PG Protocol Support Limitations</h3>
<p>
At this time, only a subset of the PostgreSQL network protocol is implemented.
Also, there may be compatibility problems on the SQL level, with the catalog, or with text encoding.
Problems are fixed as they are found.
Currently, statements can not be canceled when using the PG protocol.
Also, H2 does not provide index meta over ODBC.
</p>
<p>
PostgreSQL ODBC Driver Setup requires a database password; that means it
is not possible to connect to H2 databases without password. This is a limitation
of the ODBC driver.
</p>
<h3>Security Considerations</h3>
<p>
Currently, the PG Server does not support challenge response or encrypt passwords.
This may be a problem if an attacker can listen to the data transferred between the ODBC driver
and the server, because the password is readable to the attacker.
Also, it is currently not possible to use encrypted SSL connections.
Therefore the ODBC driver should not be used where security is important.
</p>
<p>
The first connection that opens a database using the PostgreSQL server needs to be an administrator user.
Subsequent connections don't need to be opened by an administrator.
</p>
<h3>Using Microsoft Access</h3>
<p>
When using Microsoft Access to edit data in a linked H2 table, you may need to enable the following option:
Tools - Options - Edit/Find - ODBC fields.
</p>
<h2 id="acid">ACID</h2>
<p>
In the database world, ACID stands for:
</p>
<ul>
<li>Atomicity: transactions must be atomic, meaning either all tasks are performed or none.
</li><li>Consistency: all operations must comply with the defined constraints.
</li><li>Isolation: transactions must be isolated from each other.
</li><li>Durability: committed transaction will not be lost.
</li></ul>
<h3>Atomicity</h3>
<p>
Transactions in this database are always atomic.
</p>
<h3>Consistency</h3>
<p>
By default, this database is always in a consistent state.
Referential integrity rules are enforced except when
explicitly disabled.
</p>
<h3>Isolation</h3>
<p>
For H2, as with most other database systems, the default isolation level is 'read committed'.
This provides better performance, but also means that transactions are not completely isolated.
H2 supports the transaction isolation levels 'read uncommitted', 'read committed', 'repeatable read',
and 'serializable'.
</p>
<h3>Durability</h3>
<p>
This database does not guarantee that all committed transactions survive a power failure.
Tests show that all databases sometimes lose transactions on power failure (for details, see below).
Where losing transactions is not acceptable, a laptop or UPS (uninterruptible power supply) should be used.
If durability is required for all possible cases of hardware failure, clustering should be used,
such as the H2 clustering mode.
</p>
<h2 id="durability_problems">Durability Problems</h2>
<p>
Complete durability means all committed transaction survive a power failure.
Some databases claim they can guarantee durability, but such claims are wrong.
A durability test was run against H2, HSQLDB, PostgreSQL, and Derby.
All of those databases sometimes lose committed transactions.
The test is included in the H2 download, see <code>org.h2.test.poweroff.Test</code>.
</p>
<h3>Ways to (Not) Achieve Durability</h3>
<p>
Making sure that committed transactions are not lost is more complicated than it seems first.
To guarantee complete durability, a database must ensure that the log record is on the hard drive
before the commit call returns. To do that, databases use different methods. One
is to use the 'synchronous write' file access mode. In Java, <code>RandomAccessFile</code>
supports the modes <code>rws</code> and <code>rwd</code>:
</p>
<ul>
<li><code>rwd</code>: every update to the file's content is written synchronously to the underlying storage device.
</li><li><code>rws</code>: in addition to <code>rwd</code>, every update to the metadata is written synchronously.</li>
</ul>
<p>
A test (<code>org.h2.test.poweroff.TestWrite</code>) with one of those modes achieves
around 50 thousand write operations per second.
Even when the operating system write buffer is disabled, the write rate is around 50 thousand operations per second.
This feature does not force changes to disk because it does not flush all buffers.
The test updates the same byte in the file again and again. If the hard drive was able to write at this rate,
then the disk would need to make at least 50 thousand revolutions per second, or 3 million RPM
(revolutions per minute). There are no such hard drives. The hard drive used for the test is about 7200 RPM,
or about 120 revolutions per second. There is an overhead, so the maximum write rate must be lower than that.
</p>
<p>
Calling <code>fsync</code> flushes the buffers. There are two ways to do that in Java:
</p>
<ul>
<li><code>FileDescriptor.sync()</code>. The documentation says that this forces all system
buffers to synchronize with the underlying device.
This method is supposed to return after all in-memory modified copies of buffers associated with this file descriptor
have been written to the physical medium.
</li><li><code>FileChannel.force()</code>. This method is supposed
to force any updates to this channel's file to be written to the storage device that contains it.
</li></ul>
<p>
By default, MySQL calls <code>fsync</code> for each commit. When using one of those methods, only around 60 write operations
per second can be achieved, which is consistent with the RPM rate of the hard drive used.
Unfortunately, even when calling <code>FileDescriptor.sync()</code> or
<code>FileChannel.force()</code>,
data is not always persisted to the hard drive, because most hard drives do not obey
<code>fsync()</code>: see
<a href="https://hardware.slashdot.org/story/05/05/13/0529252/your-hard-drive-lies-to-you">Your Hard Drive Lies to You</a>.
In Mac OS X, <code>fsync</code> does not flush hard drive buffers. See
<a href="https://lists.apple.com/archives/darwin-dev/2005/Feb/msg00072.html">Bad fsync?</a>.
So the situation is confusing, and tests prove there is a problem.
</p>
<p>