Select statement puts lock on the tables in Oracle 9.2.0.1.0
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Select statement puts lock on the tables in Oracle 9.2.0.1.0

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    Select statement puts lock on the tables in Oracle 9.2.0.1.0

    Hi,

    Select statement puts lock on the tables in Oracle 9.2.0.1.0.

    This happens when I run a stored procedure, which contains select statement.

    When I run the same stored procedure at Oracle 8.1.7, the tables are not getting locked.

    The lock put on 9i is Row-S(SS), which is generally should be put if there is statement SELECT FOR UPDATE. Infact in the stored procedure there is no select for update.

    Any one has faced this problem..?

    Thanks..

  2. #2
    Join Date
    Jan 2003
    Posts
    78
    Are those tables involved in parenet child relationships?

    As per metalink: "Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table."

    Refer Doc ID: 223303.1 on metalink for details.
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sgodbole
    Are those tables involved in parenet child relationships?

    As per metalink: "Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table."

    Refer Doc ID: 223303.1 on metalink for details.
    Yeah, but the point here is that skdas observes Row-SS locks when isuing plain SELECT statement - no DML, no select-for-update. SELECT shouldn't put any locks whatsoever! So I kind of doubt that skdas observations are correct - there should be some other reason for tose locks, not just plain SELECTs.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width