Results 1 to 6 of 6

Thread: Locking Problem

  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Unanswered: Locking Problem

    Hi All,

    I have row level locking for a table. In one session i enter

    begin work;
    update emp
    set name ="jack"
    where empno=1

    In second sesssion if i say

    select * from emp
    it gives me an error, cannot do a physical fetch on the table,
    but my table has row level locking.

    If i update the 10th row in one session, in other session i can view the 1st 9 rows, after that it gives me the same error. This should not happen since table is locked in row mode, it should lock only the current row and display the other rows.

    IF i give

    set isolation to dirty read;
    select * from emp;
    i can view all the data but with name with latest changes which may or may not be committed, thus my MIS may go haywire.

    Please advice. Appreciate ur prompt reply.

    Regards,

    lloyd

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Locking Problem

    Originally posted by lloydnwo
    Hi All,

    I have row level locking for a table. In one session i enter

    begin work;
    update emp
    set name ="jack"
    where empno=1

    In second sesssion if i say

    select * from emp
    it gives me an error, cannot do a physical fetch on the table,
    but my table has row level locking.

    If i update the 10th row in one session, in other session i can view the 1st 9 rows, after that it gives me the same error. This should not happen since table is locked in row mode, it should lock only the current row and display the other rows.

    IF i give

    set isolation to dirty read;
    select * from emp;
    i can view all the data but with name with latest changes which may or may not be committed, thus my MIS may go haywire.

    Please advice. Appreciate ur prompt reply.

    Regards,

    lloyd
    Hi,

    Please check lock mode of the tables, must be in lock mode row.

    Gustavo.

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Re: Locking Problem

    Originally posted by gurey
    Hi,

    Please check lock mode of the tables, must be in lock mode row.

    Gustavo.
    Hi Gustavo,

    The lock mode of the tables is 'row' mode, but still it gives the same error, "cannot do a physical fetch". Ideally it should only lock the current row, but it still locsk the whole sets of rows.

    Regards,

    lloyd

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Locking Problem

    Originally posted by lloydnwo
    Hi Gustavo,

    The lock mode of the tables is 'row' mode, but still it gives the same error, "cannot do a physical fetch". Ideally it should only lock the current row, but it still locsk the whole sets of rows.

    Regards,

    lloyd
    Hi,

    Ok, this SQL, run in one CURSOR or from dbaccess.
    Check of isolation level of session.

    Gustavo.

  5. #5
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Re: Locking Problem

    Originally posted by gurey
    Hi,

    Ok, this SQL, run in one CURSOR or from dbaccess.
    Check of isolation level of session.

    Gustavo.
    Hi Gustavo,

    First session - dbaccess

    begin work;
    update emp
    set name ="jack"
    where empno=1

    second sesssion

    select * from emp

    It gives an err, cannot do a physical fetch error, i cannot view any rows from the table inspite of row level locking. The default isolation is committed read. When i set isolation to dirty read then i can view all the rows, but with with latest changes which may or may not be committed, this could be very crucial, & my MIS may go haywire.

    Regards,

    lloyd

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Locking Problem

    Originally posted by lloydnwo
    Hi Gustavo,

    First session - dbaccess

    begin work;
    update emp
    set name ="jack"
    where empno=1

    second sesssion

    select * from emp

    It gives an err, cannot do a physical fetch error, i cannot view any rows from the table inspite of row level locking. The default isolation is committed read. When i set isolation to dirty read then i can view all the rows, but with with latest changes which may or may not be committed, this could be very crucial, & my MIS may go haywire.

    Regards,

    lloyd
    Hi,

    I do not want to discourage you, but this form of work this well.
    The update lock the row and places a lock adjacent in the indexes, to be able to jump this you should change the isolation level.

    Gustavo.

Posting Permissions

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