Hi,

Suppose We have 2 tables tab1 and tab2 involved in parent-
child relationship where column name dd in tab2 refer to
column name dd in tab1. If we delete record from
tab2 ...sql server puts shared lock on tab1 so that it
should not violate foreign key constraint ...In oracle if
we have index on column dd in table tab2 then optimizer
place lock on index instead of table.....but in sql server
even if we have index then also optimizer is placing lock
on table.....
Is this expected behaviour or we can avoid shared lock on
child table while deleting row from parent table??

Thanks
--Harvinder

Report this post to a moderator

Harvinder,

You mention deleting from tab2, which is the child table, but you also ask
about deleting from the parent table. Are you using cascading deletes, or
the standard type of foreign key? Are you using SQL Server 2000?

If you are not using cascading delete, you must delete from the child table
first, and from what I can see, SQL Server 2000 only locks the indexes
including the clustered key, which is the row, and does not lock anything on
the parent table.

Ron
--
Ron Talmage
SQL Server MVP


"harvinder" <harvinder.singh@metratech.com> wrote in message
news:71a201c26e36$5efc5640$2ae
2c90a@phx.gbl...
> Hi,
>
> Suppose We have 2 tables tab1 and tab2 involved in parent-
> child relationship where column name dd in tab2 refer to
> column name dd in tab1. If we delete record from
> tab2 ...sql server puts shared lock on tab1 so that it
> should not violate foreign key constraint ...In oracle if
> we have index on column dd in table tab2 then optimizer
> place lock on index instead of table.....but in sql server
> even if we have index then also optimizer is placing lock
> on table.....
> Is this expected behaviour or we can avoid shared lock on
> child table while deleting row from parent table??
>
> Thanks
> --Harvinder
>

Report this post to a moderator