Select statement puts lock on the tables in Oracle 9.2.0.1.0

[skdas]

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..

[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.

[jmodic]

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.