db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1822) LOCK TABLE example and description in reference manual should get replaced by a 'real' example
Date Wed, 27 Sep 2006 17:30:51 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1822?page=comments#action_12438181 ] 
            
Mamta A. Satoor commented on DERBY-1822:
----------------------------------------

Laura, I took a look at the page and the current structure of toursdb and have come up with
following example that can be used on the LOCK TABLE page. Hope this helps

1)First of all, there is a typo in the first paragraph under Syntax. Rather than 'Hotels',
it should say 'Fights'. It should read as following
"Once 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 Flights 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 Flights forces
the exclusive lock to be table-level as well."

2)The updated example can look as follows

-- lock the entire table in share mode to avoid
-- a large number of row locks
LOCK TABLE Flights IN SHARE MODE;
SELECT *
FROM Flights
WHERE orig_airport > 'OOO';

-- lock the entire table in exclusive mode
-- for a transaction that will update many rows,
-- but where no single statement will update enough rows
-- to 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.
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 FlightAvailability
SET economy_seats_taken  = (economy_seats_taken   + 2)
WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-11');

UPDATE FlightAvailability
SET economy_seats_taken  = (economy_seats_taken   + 2)
WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-12');

UPDATE FlightAvailability
SET economy_seats_taken  = (economy_seats_taken   + 2)
WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-15');

-- if a transaction needs to look at a table before
-- updating it, acquire an exclusive lock before
-- selecting to avoid deadlocks
LOCK TABLE Maps IN EXCLUSIVE MODE;
SELECT MAX(map_id) + 1 from Maps;
-- INSERT INTO MAPS . . .


> LOCK TABLE example and description in reference manual should get replaced by a 'real'
example
> ----------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1822
>                 URL: http://issues.apache.org/jira/browse/DERBY-1822
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions: 10.2.1.5, 10.3.0.0
>            Reporter: Myrna van Lunteren
>            Priority: Minor
>             Fix For: 10.3.0.0
>
>
> The Example in the documentation for LOCK TABLE looks jumbled.
> It looks like part of it is based on a demo that was part of the pre-quel to Derby. The
current toursdb demo is based on it, and thus includes reference to the table 'Flights', so
it looks as if the queries *might* be possible, but the table listed (HotelAvailability) does
not exist. 
> Then there is mention of the table People, which doesn't exist either, 
> A proper example needs to be put in place.
> Note that the description above the example mentions Hotels, this probably was supposed
to be removed also.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message