Hi Dan,
Your interpretation of these locks is more precise than mine. Since the create unique index put shared locks on some of the SYSCOLUMNS rows, the nested transaction can't get exclusive locks on those rows and hence it reverts back to using the parent transaction.
Now that we understand the current functionality, do we still see a need for improvement here? Is there a bug here which we need to address? I think this is an edge case where ddl is in the same transaction as dml and that's why there are locking issues.
Mamta
On 12/27/05, Daniel John Debrunner <[EMAIL PROTECTED]> wrote:
Mamta Satoor wrote:
> To narrow down the locking issue, I have moved the create table outside
> the create unique index transaction as follows. Also, I have added sql
> to dump lock table for syscolumns to see what kind of locks exist on
> SYSCOLUMNS at various points.
>
<snip>
> So, the create index is getting intent share lock on SYSCOLUMNS table
> and share locks on some of the rows. This prevents the nested
> transaction for generated keys logic from getting an intent exclusive on
> table and exclusive lock on the rows.
I don't think the intent share lock on SYSCOLUMNS will prevent the
nested transaction getting an intent exclusive locks. Such table level
locks just indicate that the transaction has the intent to get shared or
exclusive row locks, thus the intent table locks are compatible with
each other.
I think only the locks on the rows representing the columns of the table
are in conflict.
Thanks,
Dan.
