_docs_en.utf8.txt

非常棒的java数据库

@advanced_1000_h1
Advanced Topics

@advanced_1001_a
Result Sets

@advanced_1002_a
Large Objects

@advanced_1003_a
Linked Tables

@advanced_1004_a
Transaction Isolation

@advanced_1005_a
Multi-Version Concurrency Control (MVCC)

@advanced_1006_a
Clustering / High Availability

@advanced_1007_a
Two Phase Commit

@advanced_1008_a
Compatibility

@advanced_1009_a
Run as Windows Service

@advanced_1010_a
ODBC Driver

@advanced_1011_a
ACID

@advanced_1012_a
Durability Problems

@advanced_1013_a
Using the Recover Tool

@advanced_1014_a
File Locking Protocols

@advanced_1015_a
Protection against SQL Injection

@advanced_1016_a
Restricting Class Loading and Usage

@advanced_1017_a
Security Protocols

@advanced_1018_a
Universally Unique Identifiers (UUID)

@advanced_1019_a
Settings Read from System Properties

@advanced_1020_a
Setting the Server Bind Address

@advanced_1021_a
Glossary and Links

@advanced_1022_h2
Result Sets

@advanced_1023_h3
Limiting the Number of Rows

@advanced_1024_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 LIMIT in a query (example: SELECT * FROM TEST LIMIT 100), or by using Statement.setMaxRows(max).

@advanced_1025_h3
Large Result Sets and External Sorting

@advanced_1026_p
For result set larger than 1000 rows, the result is buffered to disk. If ORDER BY 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.

@advanced_1027_h2
Large Objects

@advanced_1028_h3
Storing and Reading Large Objects

@advanced_1029_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 PreparedStatement.setBinaryStream. To store a CLOB, use PreparedStatement.setCharacterStream. To read a BLOB, use ResultSet.getBinaryStream, and to read a CLOB, use ResultSet.getCharacterStream. If the client/server mode is used, the BLOB and CLOB data is fully read into memory when accessed. In this case, the size of a BLOB or CLOB is limited by the memory.

@advanced_1030_h2
Linked Tables

@advanced_1031_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 CREATE LINKED TABLE statement:

@advanced_1032_p
It is then possible to access the table in the usual way. There is a restriction when inserting data to this table: When inserting or updating rows into the table, NULL and values that are not set in the insert statement are both inserted as NULL. This may not have the desired effect if a default value in the target table is other than NULL.

@advanced_1033_p
For each linked table a new connection is opened. This can be a problem for some databases when using many linked tables. For Oracle XE, the maximum number of connection can be increased. Oracle XE needs to be restarted after changing these values:

@advanced_1034_h2
Transaction Isolation

@advanced_1035_p
This database supports the following transaction isolation levels:

@advanced_1036_b
Read Committed

@advanced_1037_li
This is the default level.  Read locks are released immediately.  Higher concurrency is possible when using this level.

@advanced_1038_li
To enable, execute the SQL statement    'SET LOCK_MODE 3'

@advanced_1039_li
or append ;LOCK_MODE=3 to the database URL: jdbc:h2:~/test;LOCK_MODE=3

@advanced_1040_b
Serializable

@advanced_1041_li
To enable, execute the SQL statement    'SET LOCK_MODE 1'

@advanced_1042_li
or append ;LOCK_MODE=1 to the database URL: jdbc:h2:~/test;LOCK_MODE=1

@advanced_1043_b
Read Uncommitted

@advanced_1044_li
This level means that transaction isolation is disabled.

@advanced_1045_li
To enable, execute the SQL statement    'SET LOCK_MODE 0'

@advanced_1046_li
or append ;LOCK_MODE=0 to the database URL: jdbc:h2:~/test;LOCK_MODE=0

@advanced_1047_p
When using the isolation level 'serializable', dirty reads, non-repeatable reads, and phantom reads are prohibited.

@advanced_1048_b
Dirty Reads

@advanced_1049_li
Means a connection can read uncommitted changes made by another connection.

@advanced_1050_li
Possible with: read uncommitted

@advanced_1051_b
Non-Repeatable Reads

@advanced_1052_li
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.

@advanced_1053_li
Possible with: read uncommitted, read committed

@advanced_1054_b
Phantom Reads

@advanced_1055_li
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.

@advanced_1056_li
Possible with: read uncommitted, read committed

@advanced_1057_h3
Table Level Locking

@advanced_1058_p
The database allows multiple concurrent connections to the same database. To make sure all connections only see consistent data, table level locking is used by default. This mechanism does not allow high concurrency, but is very fast. Shared locks and exclusive locks are supported. Before reading from a table, the database tries to add a shared lock to the table (this is only possible if there is no exclusive lock on the object by another connection). If the shared lock is added successfully, the table can be read. It is allowed that other connections also have a shared lock on the same object. If a connection wants to write to a table (update or delete a row), an exclusive lock is required. To get the exclusive lock, other connection must not have any locks on the object. After the connection commits, all locks are released. This database keeps all locks in memory.

@advanced_1059_h3
Lock Timeout

@advanced_1060_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.

@advanced_1061_h2
Multi-Version Concurrency Control (MVCC)

@advanced_1062_p
The MVCC feature allows higher concurrency than using (table level or row level) locks. When using MVCC in this database, delete, insert and update operations will only issue a shared lock on the table. Table are still locked exclusively when adding or removing columns, when dropping the table, and when using SELECT ... FOR UPDATE. 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 update the same row, this database fails fast: a concurrent update exception is thrown.

@advanced_1063_p
To use the MVCC feature, append MVCC=TRUE to the database URL:

@advanced_1064_p
The MVCC feature is not fully tested yet.

@advanced_1065_h2
Clustering / High Availability

@advanced_1066_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.

@advanced_1067_p
Clustering can only be used in the server mode (the embedded mode does not support clustering). It is possible to restore the cluster without stopping the server, however it is critical that no other application is changing the data in the first database while the second database is restored, so restoring the cluster is currently a manual process.

@advanced_1068_p
To initialize the cluster, use the following steps:

@advanced_1069_li
Create a database

@advanced_1070_li
Use the CreateCluster tool to copy the database to another location and initialize the clustering.  Afterwards, you have two databases containing the same data.

@advanced_1071_li
Start two servers (one for each copy of the database)

@advanced_1072_li
You are now ready to connect to the databases with the client application(s)

@advanced_1073_h3
Using the CreateCluster Tool

@advanced_1074_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.

@advanced_1075_li
Create two directories: server1 and server2.  Each directory will simulate a directory on a computer.

@advanced_1076_li
Start a TCP server pointing to the first directory.  You can do this using the command line:

@advanced_1077_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:

@advanced_1078_li
Use the CreateCluster tool to initialize clustering.  This will automatically create a new, empty database if it does not exist.  Run the tool on the command line:

@advanced_1079_li
You can now connect to the databases using an application or the H2 Console using the JDBC URL jdbc:h2:tcp://localhost:9101,localhost:9102/test

@advanced_1080_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.

@advanced_1081_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 CreateCluster tool.

@advanced_1082_h3
Clustering Algorithm and Limitations

@advanced_1083_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: RANDOM_UUID(), SECURE_RAND(), SESSION_ID(), MEMORY_FREE(), MEMORY_USED(), CSVREAD(), CSVWRITE(), RAND() [when not using a seed]. Those functions should not be used directly in modifying statements (for example INSERT, UPDATE, or MERGE). However, they can be used in read-only statements and the result can then be used for modifying statements.

@advanced_1084_h2
Two Phase Commit

@advanced_1085_p
The two phase commit protocol is supported. 2-phase-commit works as follows:

@advanced_1086_li
Autocommit needs to be switched off

@advanced_1087_li
A transaction is started, for example by inserting a row

@advanced_1088_li
The transaction is marked 'prepared' by executing the SQL statement <code>PREPARE COMMIT transactionName</code>

@advanced_1089_li
The transaction can now be committed or rolled back

@advanced_1090_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'

@advanced_1091_li
When re-connecting to the database, the in-doubt transactions can be listed  with <code>SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT</code>