The JDBC Connection and Transaction Model

Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 87142 invoked from network); 24 Jul 2008 19:35:15 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 24 Jul 2008 19:35:15 -0000 Received: (qmail 79792 invoked by uid 500); 24 Jul 2008 19:35:15 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 79772 invoked by uid 500); 24 Jul 2008 19:35:15 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 79760 invoked by uid 99); 24 Jul 2008 19:35:14 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Jul 2008 12:35:14 -0700 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Jul 2008 19:34:28 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 5C31F2388892; Thu, 24 Jul 2008 12:34:54 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r679509 - in /db/derby/docs/trunk/src: devguide/ ref/ Date: Thu, 24 Jul 2008 19:34:53 -0000 To: derby-commits@db.apache.org From: chaase3@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080724193454.5C31F2388892@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: chaase3 Date: Thu Jul 24 12:34:52 2008 New Revision: 679509 URL: http://svn.apache.org/viewvc?rev=679509&view=rev Log: DERBY-3579: The Developer's Guide incorrectly describes the behavior of transactions inside procedures and functions Developer's Guide: Modified 12 topics related to server-side programming to use "routine," "procedure," or "function" as appropriate. Reference Manual: Modified 1 topic to use "routine". Patch: DERBY-3579-3.diff Modified: db/derby/docs/trunk/src/devguide/cdevconcepts29416.dita db/derby/docs/trunk/src/devguide/cdevconcepts32861.dita db/derby/docs/trunk/src/devguide/cdevconcepts36881.dita db/derby/docs/trunk/src/devguide/cdevconcepts38375.dita db/derby/docs/trunk/src/devguide/cdevdgpref11181.dita db/derby/docs/trunk/src/devguide/cdevspecial.dita db/derby/docs/trunk/src/devguide/cdevspecial16181.dita db/derby/docs/trunk/src/devguide/cdevspecial28907.dita db/derby/docs/trunk/src/devguide/cdevspecial29620.dita db/derby/docs/trunk/src/devguide/cdevspecial41728.dita db/derby/docs/trunk/src/devguide/cdevspecial42117.dita db/derby/docs/trunk/src/devguide/cdevspecial847513.dita db/derby/docs/trunk/src/ref/crefjavgcasyn.dita Modified: db/derby/docs/trunk/src/devguide/cdevconcepts29416.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevconcepts29416.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevconcepts29416.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevconcepts29416.dita Thu Jul 24 12:34:52 2008 @@ -52,13 +52,13 @@ next method before it can accessed again. In this state, the only other valid operation besides calling next is calling close.

Database-side JDBC Procedures (procedures using nested connections) -

You cannot execute procedures within SQL statements if those procedures -perform a commit or rollback on the current connection. Since in the auto-commit +

Database-side JDBC routines (routines using nested connections) +

You cannot execute functions within SQL statements if those functions +perform a commit or rollback on the current connection. Since in auto-commit mode all SQL statements are implicitly committed, turns -off auto-commit during execution of database-side procedures and turns it -back on when the method completes.

Procedures that use nested connections -are not permitted to turn auto-commit on or off or to commit or roll back.

+off auto-commit during execution of database-side routines and turns it +back on when the statement completes.

Routines that use nested connections +are not permitted to turn auto-commit on or off.

Table-level locking and the SERIALIZABLE isolation level

When an application uses table-level locking and the SERIALIZABLE isolation level, all statements that access tables hold at least shared table locks. Shared @@ -84,7 +84,7 @@ Commit() or rollback() completes a transaction. -Database-side JDBC procedures (routines using nested connections) +Database-side JDBC routines (routines that use nested connections) Auto-commit is turned off. Works (no explicit commits or rollbacks are allowed). Modified: db/derby/docs/trunk/src/devguide/cdevconcepts32861.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevconcepts32861.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevconcepts32861.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevconcepts32861.dita Thu Jul 24 12:34:52 2008 @@ -50,7 +50,7 @@ // note: do not catch such exceptions in database-side methods; // catch such exceptions only at the outermost level of // application code. -// See Database-side JDBC procedures and SQLExceptions. +// See . catch (SQLException se) { if (se.getSQLState().equals("40001")) { // it was chosen as a victim of a deadlock. Modified: db/derby/docs/trunk/src/devguide/cdevconcepts36881.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevconcepts36881.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevconcepts36881.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevconcepts36881.dita Thu Jul 24 12:34:52 2008 @@ -27,14 +27,14 @@ to multiple databases -

Within the scope of one Connection, you access +

Within the scope of one Connection, you access only a single database. -(Database-side JDBC procedures can allow you to access more than one database -in some circumstances.) A single application might allow one or more Connections to Connections to , either to a single database or to many different databases, provided that all the databases are within the same system.

-
With DriverManager, you use the connection URL as an argument to get the getConnection method +
With DriverManager, you use the connection URL as an argument to get the getConnection method to specify which database to connect to and other details.

The following example shows an application establishing three separate connections to two different databases in the current system.
Modified: db/derby/docs/trunk/src/devguide/cdevconcepts38375.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevconcepts38375.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevconcepts38375.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevconcepts38375.dita Thu Jul 24 12:34:52 2008 @@ -19,16 +19,16 @@ limitations under the License. --> -The JDBC Connection and Transaction Model +The JDBC connection and transaction model Session and transaction capabilities for SQL are handled through -JDBC procedures, not by SQL commands. +JDBC routines, not by SQL commands. -JDBC ConnectionsTransactions +JDBC connectionsTransactions
JDBC defines a system session and transaction model for database access. A session is the duration of one connection to the database and is -handled by a JDBC Connection object.
+handled by a JDBC Connection object.
Modified: db/derby/docs/trunk/src/devguide/cdevdgpref11181.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevdgpref11181.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevdgpref11181.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevdgpref11181.dita Thu Jul 24 12:34:52 2008 @@ -26,6 +26,6 @@
It describes basic concepts, such as how you create and access databases -through JDBC procedures and how you can deploy applications.
+through JDBC routines and how you can deploy applications.
Modified: db/derby/docs/trunk/src/devguide/cdevspecial.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecial.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevspecial.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevspecial.dita Thu Jul 24 12:34:52 2008 @@ -21,7 +21,7 @@ Derby server-side programming This section discusses special programming for . -
In particular, this section discusses how to program database-side JDBC procedures, +
In particular, this section discusses how to program database-side JDBC routines, triggers, and table functions.
Modified: db/derby/docs/trunk/src/devguide/cdevspecial16181.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecial16181.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevspecial16181.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevspecial16181.dita Thu Jul 24 12:34:52 2008 @@ -19,20 +19,20 @@ limitations under the License. --> -Database-side JDBC procedures using non-nested connections -A database-side JDBC procedure can create a new connection instead -of using a nested connection. Statements executed in the procedure will be +Database-side JDBC routines using non-nested connections +A database-side JDBC routine can create a new connection instead +of using a nested connection. Statements executed in the routine will be part of a different transaction, and so can issue commits and rollbacks. -Database-side JDBC proceduresusing non-nested +Database-side JDBC routinesusing non-nested connections -
Such procedures can connect to a database different from the one to which -the parent SQL statement that called it is connected. The procedure does not -use the same transaction or Connection. It establishes a new Connection and +
Such a routine can connect to a database different from the one to which +the parent SQL statement that called it is connected. The routine does not +use the same transaction or Connection. It establishes a new Connection and transaction.
-If database-side JDBC procedures do not use nested connections, this +If database-side JDBC routines do not use nested connections, this means that they are operating outside of the normal DBMS transaction control, so it is not good practice to use them indiscriminately. Modified: db/derby/docs/trunk/src/devguide/cdevspecial28907.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecial28907.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevspecial28907.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevspecial28907.dita Thu Jul 24 12:34:52 2008 @@ -19,23 +19,23 @@ limitations under the License. --> -Requirements for database-side JDBC procedures using nested connections +Requirements for database-side JDBC routines using nested connections In order to preserve transactional atomicity, database-side JDBC -procedures that use nested connections have the following limitations. +routines that use nested connections have the following limitations. Nested connectionslimitations of

-
Cannot issue a commit or rollback, unless called within a CALL statement. +
Can issue a commit or rollback only within a procedure (not a function).
-
Cannot change connection attributes such as auto-commit.
+
Cannot change the auto-commit connection attribute.

Cannot modify the data in a table used by the parent statement that called -the procedure, using INSERT, UPDATE, or DELETE. For example, if a SELECT statement +the routine, using INSERT, UPDATE, or DELETE. For example, if a SELECT statement using the T table calls the changeTables procedure, changeTables cannot -modify data in the T table.
-
Cannot drop a table used by the statement that called the procedure.
+modify data in the T table. +
Cannot drop a table used by the statement that called the routine.

Cannot be in a class whose static initializer executes DDL statements.

In addition, the Connection object that represents the nested connection always has its auto-commit mode set to false.
Modified: db/derby/docs/trunk/src/devguide/cdevspecial29620.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecial29620.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevspecial29620.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevspecial29620.dita Thu Jul 24 12:34:52 2008 @@ -19,29 +19,30 @@ limitations under the License. --> -Database-side JDBC procedures and nested connections -Most database-side JDBC Procedures need to share the same transaction +Database-side JDBC routines and nested connections +Most database-side JDBC routines need to share the same transaction space as the statements that called them. Nested connectionsgetting -Database-side JDBC proceduresusing nested connections -Nested connectionsgetting +Database-side JDBC routinesusing nested connections +
The reasons for this are:

to avoid blocking and deadlocks
-
to ensure that any updates done from within the method are atomic with +
to ensure that any updates done from within the routine are atomic with the outer transaction

-
In order to use the same transaction, the procedure must use the same connection -as the parent SQL statement in which the method was executed. Connections +
In order to use the same transaction, the routine must use the same connection +as the parent SQL statement in which the routine was executed. Connections re-used in this way are called nested connections.
-
Use the connection URL jdbc:default:connection to re-use the current Connection.
-
The Database Connection URL jdbc:default:connection allows a Java method +
Use the connection URL jdbc:default:connection to re-use the current Connection.
+
The database donnection URL jdbc:default:connection allows a Java method to get the Connection of the SQL statement that called it. This is -the standard (SQL standard, Part 13 SQL Routines and Java) mechanism to obtain -the nested connection object. The method would get a Connection:
+the standard (SQL standard, Part 13, SQL Routines and Java) mechanism to obtain +the nested connection object. The method would get a Connection +as follows:
Connection conn = DriverManager.getConnection( "jdbc:default:connection");
URL attributes are not supported as part of this connection URL. Any URL Modified: db/derby/docs/trunk/src/devguide/cdevspecial41728.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecial41728.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevspecial41728.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevspecial41728.dita Thu Jul 24 12:34:52 2008 @@ -19,17 +19,17 @@ limitations under the License. --> -Database-side JDBC procedures and SQLExceptions -It is possible to code database-side procedures, like application-side +Database-side JDBC routines and SQLExceptions +It is possible to code database-side routines, like application-side methods, to catch SQLExceptions. SQLExceptions that are caught within -a procedure are hidden from the calling application code. +a routine are hidden from the calling application code. SQLExceptionsimportance of not catching within -database-side proceduresDatabase-side JDBC -proceduresand SQLExceptions +database-side routinesDatabase-side JDBC +routinesand SQLExceptions -
When such SQLExceptions are of transaction severity +
When such SQLExceptions are of transaction severity (such as deadlocks), this "hiding" of the exception causes unexpected problems.

This is because errors of transaction severity roll back work already done by a transaction (not just the piece executed by the called method) and silently @@ -53,7 +53,7 @@
Not all database vendors handle nested deadlocks the same way. For this and other reasons, it is not possible to write portable SQL-invoking methods. However, it is possible to write SQL-invoking methods that behave identically regardless -of whether you invoke them in the application or as a procedure in the database.
+of whether you invoke them in the application or as a routine in the database.

In order to ensure identical application- and database-side handling of nested errors, code try-catch blocks to check for the severity of exceptions as follows:
Modified: db/derby/docs/trunk/src/devguide/cdevspecial42117.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecial42117.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevspecial42117.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevspecial42117.dita Thu Jul 24 12:34:52 2008 @@ -18,17 +18,17 @@ limitations under the License. --> -Programming database-side JDBC procedures +Programming database-side JDBC routines Methods invoked within an application are called application-side methods. Methods invoked within are -called database-side procedures. +called database-side routines. -Database-side JDBC procedures +Database-side JDBC routines -
An application-side method can be exactly the same as a database-side procedure. +
An application-side method can be exactly the same as a database-side routine. The only difference is where you invoke them. You write the method only once. -Where you invoke the method-within the application or within an SQL statement-determines +Where you invoke the method--within the application or within an SQL statement--determines whether it is an "application-side" or a "database-side" method.
Modified: db/derby/docs/trunk/src/devguide/cdevspecial847513.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevspecial847513.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevspecial847513.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevspecial847513.dita Thu Jul 24 12:34:52 2008 @@ -23,19 +23,16 @@ If a procedure uses only IN parameters, can execute the procedure by using the SQL CALL command. A stored procedure with IN, OUT, or INOUT parameters can be invoked from a client application by using -the CallableStatement method. +a CallableStatement. -Database-side JDBC proceduresexample of connecting -to different database +Database-side JDBC proceduresinvoking with CALL command
You can invoke the procedure in an SQL statement such as the following:
CALL MYPROC() -You cannot roll back this statement, because commits occur within the -procedure itself. Procedures that use nested connections, on the other hand, -are not permitted to commit or roll back and can therefore be rolled back -after the calling statement. -
You can also use the CALL command to execute a procedure that does return +You can roll back a CALL statement only if no commits or rollbacks +occur within the specified procedure. +
You can also use the CALL command to execute a routine that returns a value, but you will not be able to access the value.
Modified: db/derby/docs/trunk/src/ref/crefjavgcasyn.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/crefjavgcasyn.dita?rev=679509&r1=679508&r2=679509&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/crefjavgcasyn.dita (original) +++ db/derby/docs/trunk/src/ref/crefjavgcasyn.dita Thu Jul 24 12:34:52 2008 @@ -23,7 +23,7 @@
also supports the following SQL standard syntax to obtain a reference to the current connection -in a database-side JDBC procedure or method: jdbc:default:connection
+in a database-side JDBC routine: jdbc:default:connection