db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein.Grov...@Sun.COM (Øystein Grøvlen)
Subject Re: table locking
Date Thu, 24 Nov 2005 12:43:22 GMT

You might get a table lock when scanning the whole table (as a select
without a predicate will do).  However, this query would not succeed
even if row locking was used, since the scan would be blocked by the
lock on (c,d).  

Except when using 'read uncommitted' isolation level, a query will be
blocked when it encounters a row with uncommitted changes.


>>>>> "MS" == Mehran Sowdaey <Mehran.Sowdaey@Sun.COM> writes:

    MS> The actual default behavior that we see is table level locking when
    MS> autocommit is turned off not row level locking. Simple test:

    MS> Before lock:

    MS> Session 1, and Session 2 can see this row:
    ij> select * from mscolor;
    MS> COL1      |COL2
    MS> ---------------------
    MS> a         |b

    MS> 1 rows selected

    MS> After lock:

    MS> Session 1:
    ij> autocommit off;
    ij> insert into mscolor values ('c', 'd');
    MS> 1 row inserted/updated/deleted

    MS> Session 2:

    ij> select * from mscolor;
    MS> COL1      |COL2
    MS> ---------------------
    MS> ERROR 40XL1: A lock could not be obtained within the time requested

    MS> Session 2 can not see anything until session 1 commits or rolls back.

View raw message