Re: Checking for uncommited transactions

On 10 Mar 2003, mail_ignored_at_web.de wrote:

> Lets see why.
>
> Assume you have a table USERS and a table USER_SETTINGS. They are
> linked by the column USER_ID (one user - many settings).
> If you insert a row in the USER_SETTINGS table, Oracle ensures that a
> row in table USERS exists with the passed USER_ID (assuming declared
> ref integrity).
>
> While Oracle waits for a COMMIT/ROLLBACK the table USERS remains
> locked for DELETE operations. Consider, I speak about the table not a
> row! This happens, since Oracle has to ensure that all regarding rows
> in USERS exists until you perform a commit. Otherwise other Oracle
> users may delete the USER, because they don't see any row in
> USER_SETTINGS.
>
> This means other Oracle users cannot DELETE any row in table USERS.

What the flip are you talking about?

Lets create a table with your scenario:

    SQL> create table users(id number);

    Table created.

    SQL> create table user_settings(id number,setting_id number);

    Table created.

    SQL> alter table users add constraint pk1 primary key (id);

    Table altered.

    SQL> alter table user_settings add constraint pk2 primary key (id,setting_id);

    Table altered.

    SQL> alter table user_settings add constraint fk1 foreign key (id) references users(id);

    Table altered.

    [... inserted some rows]

    SQL> select * from users;

            ID

             1
             2

    SQL> select * from user_settings;

            ID SETTING_ID

• ---------- 1 1 1 2 1 3 2 1 2 2 2 3

    6 rows selected.

    SQL> commit;

    Commit complete.

Now, in one session:

    SQL> delete user_settings where id = 2;

    3 rows deleted.

Now in another session:

    SQL> delete user_settings where id = 1;

    3 rows deleted.

    SQL> delete users where id = 1;

    1 row deleted.

    SQL> commit;

    Commit complete.

Okay, back to the first session.

    SQL> commit;

    Commit complete.

    SQL> select * from user_settings;

    no rows selected

    SQL> select * from users;

            ID

             2

Where is this full table locking?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.