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