Perl DBI module hanging (transaction isolation)

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I am running a perl script (on ancient Perl 5.6, with which I am
stuck) which uses DBI module. The script runs  select, delete and
insert statements against an Oracle table. The script runs properly
most of the time.

I also have a 'sqlplus' session running.
There are 5 records in table tt22.

Case A)
In sqlplus session: I delete all the 5 records from the table; issue a
'commit', and run the perl script. It runs fine.

Case B)
In sqlplus session, I delete all the 5 records from the table but do
not run commit.
Then I run the perl script but it hangs.
I issue 'commit' via sqlplus, and the 'hanging' perl script starts
running at once.

I do not want my perl script to hang. Is it possible to set a
transaction isolation level via DBI (perhaps immediately after
connecting to the database  DBI->connect) which will enable the perl
script to run smoothly even when I have deleted some records in the
sqlplus session without commiting the delete action.

Re: Perl DBI module hanging (transaction isolation)

Quoted text here. Click to load it

Going by the Oracle SQL docs, you would want to use


however it seems that Oracle only supports SERIALIZABLE and READ
COMMITTED, neither of which will do what you want. (SERIALIZABLE will
cause the SELECT to fail rather than hang.) Check your Oracle
documentation to see if this applies to your version.


Re: Perl DBI module hanging (transaction isolation) wrote:
Quoted text here. Click to load it

Your definition of "properly" is improper.

Quoted text here. Click to load it

No.  Oracle does not work that way.  You may be able to do something
with the "skip locked", I think it is called.  But that will probably do
more harm than good.

Maybe you can set autocommit to on in sqlplus.

But why you have a burning desire to achieve data corruption is beyond
me.  Maybe you can switch to a database that specializes in corrupting
your data.


Re: Perl DBI module hanging (transaction isolation)

(Sorry for the bad quote, don't have the previous message.)

On Thu, 07 Jan 2010 04:12:42 +0100, Xho Jingleheimerschmidt  

Quoted text here. Click to load it

As long as you do not need CPAN modules which require 5.8 or newer, and as  
long as you do not work with Unicode, perl 5.6 is fine, especially  
together with Oracle.

This finished the perl specific part of the question, nevertheless:

Quoted text here. Click to load it

No, it does not hang. It merely waits on a lock (hard to tell which one  
without knowing what your perl script does; if you need to know, then  
Google will quickly turn up SQL queries you can use to find out which  
particular kind of lock it is).

Oracle is extremely robust in respect to "hanging" - it usually  
automatically and immediately detects real hangs (deadlocks) and aborts  
one of the involved transactions with an error.

The only kind of "hanging" you will experience is the one you have found:  
one transaction is being kept open (i.e., neither a rollback or a commit  
happens), and while it does, it can hold certain locks. Although in  
Oracle, you're lucky in that there are only very few occasions where you  
actually have to wait on another transaction. It would be interesting to  
know what exactly your perl script is trying to do.

Quoted text here. Click to load it

It does not make sense to ask the question like this. The answer to this  
particular question is "no" simply because there is no way in Oracle to  
influence locking/transaction behaviour in regard to a particular DML  
statement (i.e., specific for DELETE).

And on another level, the answer is also "no", because there *is* a reason  
why Oracle locks there. Oracle is not like other DBs that lock everything  
"just in case"; if locks do happen, there is a good reason, and usually by  
avoiding the lock, you are not doing yourself a favour at all.

Quoted text here. Click to load it

... but be sure to understand what it does (towards the original poster).  
It *will* get rid of your current problem, but maybe not in the way you  
want/expect to, and it can burn you later.

Quoted text here. Click to load it


Site Timeline