Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    9

    Select from a locked row

    Given table and functions
    PHP Code:
    TABLE tbl(
     
    i integer,
     
    a integer,
     
    txt text
    );
    FUNCTION 
    sq(nRETURNS integer AS
    $$
      RETURN 
    n^2;
    $$;
    FUNCTION 
    I() RETURNS integer AS
    $$
      RETURN 
        
    SELECT i FROM tbl
        WITH ROWID 
    as R
        WHERE r 
    CURRENT_TRANSACTION.row(); -- objdescribed in DB engine
    $$; 
    perform statement
    PHP Code:
    INSERT INTO tbl VALUES(
    2,SQ(N()),SELECT i||a WHERE i N());

     
    i   a   txt
    -----------
     
    2  4   '24' 
    I find that most (all?) databases do not allow access to column values of a row under commit. I studied solutions and would like to discuss them if this subject of interest.

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    It's the ISOLATION

    In the Databases, ACID (Atomicity, Consistency, Isolation, Durability) is a set of rules you should allways respect. So reading a non-commited row is a no-no behavior. It'll break the Isolation part of it. And also could break Consistency part, too.

    Only major difference between most databases is, what value you will get from a row, that is currently updated.

    * In Oracle, you got the old value.
    * In DB2, you had to wait until the lock is removed.

    Off course, if you read a row WITH UR option, you'll get non-commited data out, but answer may be pure bull...

    Cheers, Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •