svn commit: r451037 - /db/derby/docs/trunk/src/ref/rrefsqlj40506.dita

[fu...@apache.org]

Author: fuzzylogic
Date: Thu Sep 28 15:04:11 2006
New Revision: 451037

URL: http://svn.apache.org/viewvc?view=rev&rev=451037
Log:
DERBY-1822: Provide a better LOCK TABLE example in the reference manual.

Committed for Laura Stewart <sc...@gmail.com>

Modified:
    db/derby/docs/trunk/src/ref/rrefsqlj40506.dita

Modified: db/derby/docs/trunk/src/ref/rrefsqlj40506.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj40506.dita?view=diff&rev=451037&r1=451036&r2=451037
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj40506.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj40506.dita Thu Sep 28 15:04:11 2006
@@ -1,4 +1,5 @@
 <?xml version="1.0" encoding="utf-8"?>
+ 
 <!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
  "../dtd/reference.dtd">
 <!-- 
@@ -35,48 +36,46 @@
 <li>Avoid deadlocks</li>
 </ul></p><p>You cannot lock system tables with this statement.</p></section>
 <refsyn><title>Syntax</title> <codeblock><b>LOCK TABLE <i><xref href="rreftablename.dita#rreftablename">table-Name</xref></i> IN { SHARE | EXCLUSIVE } MODE</b
-></codeblock><p>Once a table is locked in either mode, a transaction does
+></codeblock><p>After a table is locked in either mode, a transaction does
 not acquire any subsequent row-level locks on a table.  For example, if a
 transaction locks the entire <codeph>Flights</codeph> table in share mode
 in order to read data, a particular statement might need to lock a particular
 row in exclusive mode in order to update the row. However, the previous table-level
-lock on the <codeph>Hotels</codeph> table forces the exclusive lock to be
+lock on the <codeph>Flights</codeph> table forces the exclusive lock to be
 table-level as well.</p><p>If the specified lock cannot be acquired because
 another connection already holds a lock on the table, a statement-level exception
 is raised (<i>SQLState</i> X0X02) after the deadlock timeout period.</p> </refsyn>
-<example><codeblock><b><ph>-- lock the entire table in share mode to avoid
--- a large number of row locks</ph>
-LOCK TABLE Flights IN SHARE MODE;
+<example><title>Examples</title><p>To lock the entire <codeph>Flights</codeph> table
+in share mode to avoid a large number of row locks, use the following statement:<codeblock>LOCK TABLE Flights IN SHARE MODE;
 SELECT *
 FROM Flights
 WHERE orig_airport &gt; 'OOO';
-<ph>-- lock the entire table in exclusive mode
--- for a transaction that will update many rows,
--- but where no single statement will update enough rows
--- acquire an exclusive table lock on the table.
--- In a row-level locking system, that transaction would
--- require a large number of locks or might deadlock.</ph>
-LOCK TABLE HotelAvailability IN EXCLUSIVE MODE;
-UPDATE HotelAvailability
-SET rooms_taken = (rooms_taken + 2)
-WHERE hotel_id = 194 AND booking_date = DATE('1998-04-10');
+</codeblock></p><p>You have a transaction with multiple UPDATE statements.
+Since each of the individual statements acquires only a few row-level locks,
+the transaction will not automatically upgrade the locks to a table-level
+lock. However, collectively the UPDATE statements acquire and release a large
+number of locks, which might result in deadlocks. For this type of transaction,
+you can acquire an exclusive table-level lock at the beginning of the transaction. For
+example:<codeblock>LOCK TABLE FlightAvailability IN EXCLUSIVE MODE;
+UPDATE FlightAvailability
+SET economy_seats_taken = (economy_seats_taken + 2)
+WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-03-31');
 
-UPDATE HotelAvailability
-SET rooms_taken = (rooms_taken + 2)
-WHERE hotel_id = 194 AND booking_date = DATE('1998-04-11');
+UPDATE FlightAvailability
+SET economy_seats_taken = (economy_seats_taken + 2)
+WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-11'); 
 
-UPDATE HotelAvailability
-SET rooms_taken = (rooms_taken + 2)
-WHERE hotel_id = 194 AND booking_date = DATE('1998-04-12');
+UPDATE FlightAvailability
+SET economy_seats_taken = (economy_seats_taken + 2)
+WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-12');
 
-UPDATE HotelAvailability
-SET rooms_taken = (rooms_taken + 2)
-WHERE hotel_id = 194 AND booking_date = DATE('1998-04-12');
-<ph>-- if a transaction needs to look at a table before
--- updating it, acquire an exclusive lock before
--- selecting to avoid deadlocks</ph>
-LOCK TABLE People IN EXCLUSIVE MODE;
-SELECT MAX(person_id) + 1 FROM PEOPLE;
--- INSERT INTO PEOPLE . . .</b></codeblock> </example>
+UPDATE FlightAvailability
+SET economy_seats_taken = (economy_seats_taken + 2)
+WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-15');
+</codeblock></p><p>If a transaction needs to look at a table before updating
+the table, acquire an exclusive lock before selecting to avoid deadlocks.
+For example:<codeblock>LOCK TABLE Maps IN EXCLUSIVE MODE;
+SELECT MAX(map_id) + 1 FROM Maps;
+-- INSERT INTO Maps . . .</codeblock></p> </example>
 </refbody>
 </reference>