puzzled by Toad

one colleague just showed me how to insert duplicate rows in a table that has a primary key.


create table t(x number primary key);[F5]
Table created.
insert into t values (1);[F5]
1 row created.
insert into t values (1);[F5]
1 row created.

Hey, what happened? It took me quite a while to figure out which feature of Toad was “allowing” this.

Let’s look at Toad.ini (or View – ToadOptions – Transaction)

[SETTINGS]
ALWAYSROLLBACK=1
THREADQUERIES=1

Which means, Toad is starting a separate session each time a query is run (by F5 for instance) and at the end of this child session, it rollbacks. This is not the default, but this is pure magic!

Let’s prove it :

select count(*) from t where x=1;[F5]

COUNT(*)
----------
0

1 row selected.

:mrgreen:

1 Comment

  • This why Toad can lock itself sometimes when you run scripts and have uncommitted transactions in other windows.
    Or if you try to use the Schema browser to remove a job – locks every time (for me at least – serves me right for using an IDE as a shortcut sometimes).

Leave a Reply

Your email address will not be published.