The Developer's Guide incorrectly describes the behavior of transactions inside procedures and functions

• Key: DERBY-3579
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Kim Haase
• Reporter: Rick Hillegas
• Component/s: Documentation
• Affects Version/s: 10.3.2.1
• Fix Version/s: 10.4.2.0, 10.5.1.1

Description

The "Programming database-side JDBC procedures" section of the Developer's Guide misleads users about how transactions can be managed inside database procedures. For instance, the section titled "Invoking a procedure using the CALL command" says the following: "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." This is not true. User-coded procedures can issue both commit() and rollback() on the nested connection bound to the "jdbc:default:connection" URL and those methods have the desired behavior. The whole "Programming database-side JDBC procedures" section could use an overhaul since it appears to make other misleading statements about the behavior of transactions inside routines. Thanks to Dag for pointing out the following email thread: http://www.nabble.com/Transaction-Problems-of-a-Derby-Stored-Procedure-td15494178.html

Comments

[Rick Hillegas]

Attaching z.java, which contains a database procedure, commitProc(), which can be used to demonstrate the behavior of commit()/rollback() in user-coded routines.

[Rick Hillegas]

Derby's commit/rollback behavior in user-coded procedures has not changed since 10.1.2.1.

[Kim Haase]

I'm attaching DERBY-3579.diff and DERBY-3579.zip as a start on this fix. I believe more is needed.

In "Invoking a procedure using the CALL command" (cdevspecial847513.dita) I removed the false statement "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."

In "Requirements for database-side JDBC procedures using nested connections" (cdevspecial28907.dita) I made the changes Dan suggested in the Nabble thread -- changing the intro text to say "routine" instead of "procedure", and editing the first two bullet items. I believe the other bullet items that mention procedures do in fact apply to procedures and not functions, so I left them as is. (I also corrected a minor formatting glitch.)

I didn't change the title of the second topic to say "routines" rather than "procedures", though. I think that is the next step, but it requires changing the titles and modifying the language, where appropriate, for most of the topics in this section:

Programming database-side JDBC procedures
  Database-side JDBC procedures and nested connections
    Requirements for database-side JDBC procedures using nested connections
  Database-side JDBC procedures using non-nested connections
  Database-side JDBC procedures and SQLExceptions

Of course the title "Invoking a procedure using the CALL command" really does apply only to procedures and shouldn't be changed.

I would appreciate knowing if this seems the right thing to do before I start making all these additional changes. Thanks very much.

[Kim Haase]

Meant to mark this Patch Available before, but forgot.

[Rick Hillegas]

Hi Kim,

Thanks for the patch. It looks good. However, I think that there is still a statement in cdevspecial847513 which could be misleading:

"Note: You cannot roll back this statement, because commits occur within the procedure itself."

This suggests to me that a procedure always either commits or rolls back its work. This is not the case. It all depends on how the procedure is coded. If the procedure does not explicitly call commit() or rollback() and no errors occur while the procedure executes, then the calling code can roll back the work done by the procedure.

[Kim Haase]

Attaching DERBY-3579-2.diff, DERBY-3579-2.zip, and DERBY-3579-2.stat.

I hope I have corrected the statement that was misleading in cdevspecial847513.dita. I made a few other corrections to the last sentence, and to an index entry that had nothing to do with this topic. I also changed "by using the CallableStatement method" to "by using a CallableStatement", because "method" is ambiguous and seems to have meant "technique" here (CallableStatement is an interface, not a method).

I have gone ahead and changed "procedure" to "routine" wherever it seemed appropriate to do so, but I hope someone will review the changes carefully and let me know if any corrections are needed.

In addition to the changes previously described, I have also modified the following Developer's Guide topics:

cdevspecial847513.dita, "Invoking a procedure using the CALL command":

cdevspecial.dita, "Derby server-side programming": changed "procedure" to "routine"

cdevspecial42117.dita, "Programming database-side JDBC procedures": changed "procedure" to "routine" where needed and made some other fixes

cdevspecial29620.dita, "Database-side JDBC procedures and nested connections": changed "procedure" to "routine" where needed and made other fixes (removed duplicate index term, capitalization, inconsistent fonts, etc.)

cdevspecial16181.dita, "Database-side JDBC procedures using non-nested connections": changed "procedure" to "routine" where needed, fixed font inconsistency

cdevspecial41728.dita, "Database-side JDBC procedures and SQLExceptions": changed "procedure" to "routine" where needed, fixed font inconsistency

cdevconcepts29416.dita, "Using auto-commit": changed "procedure" to "routine" where needed

cdevconcepts32861.dita, "Programming applications to handle deadlocks": removed text from cross-reference to cdevspecial41728.dita

cdevconcepts36881.dita, "Connections": changed "JDBC procedure" to "JDBC routine" (one occurrence), fixed font inconsistency

cdevconcepts38375.dita, "The JDBC Connection and Transaction Model": changed "JDBC procedure" to "JDBC routine" (one occurrence), fixed font and capitalization inconsistencies

cdevdgpref11181.dita, "Purpose of this guide": changed "JDBC procedure" to "JDBC routine" (one occurrence)

Also in the Ref Manual:

crefjavgcasyn.dita, "Additional SQL syntax": changed "database-side JDBC procedure or method" to "database-side JDBC routine"

[Rick Hillegas]

Thanks for the patch, Kim. This material is much more clear after you changed "procedure" to "routine". A couple comments:

cdevconcepts36881.dita

I would distinguish between functions and procedures:

# 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, Derby turns 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.

-------------------------

cdevconcepts36881.dita

There is a spurious right angle bracket following "getConnection" on this line:

<p>With DriverManager, you use the connection URL as an argument to get the <i>getConnection</i>> method

[Kim Haase]

Thanks very much, Rick, for catching those problems.

I think the first file you meant was cdevconcepts29416.dita (Using auto-commit). I made the changes you suggested there and fixed the typo in cdevconcepts36881.dita.

I'm attaching DERBY-3579-3.diff and DERBY-3579-3.zip. Please let me know if further changes are needed.

[Rick Hillegas]

Hi Kim,

Your latest fixes look good to me. +1

[Kim Haase]

Thanks very much, Rick.

Committed DERBY-3579-3.diff to trunk at revision 679509.

[Kim Haase]

Merged to DERBY-3579-3.diff to 10.4 docs branch at revision 681430.

[Kim Haase]

Now fixed in both trunk and branch.

[Kim Haase]

Changes appear in both Latest Alpha Manuals and in the 10.4 docs release, so this issue can be closed.