Perl DBI module hanging (transaction isolation)

Discussion in 'Perl Misc' started by dn.perl@gmail.com, Jan 6, 2010.

  1. Guest

    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.
     
    , Jan 6, 2010
    #1
    1. Advertising

  2. wrote:
    > 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.


    Your definition of "properly" is improper.

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


    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.

    Xho
     
    Xho Jingleheimerschmidt, Jan 7, 2010
    #2
    1. Advertising

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

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

    > wrote:
    >> 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.


    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:

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


    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.

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


    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.

    > Maybe you can set autocommit to on in sqlplus.


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

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


    ACK.
     
    Jochen Lehmeier, Jan 7, 2010
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jesper
    Replies:
    1
    Views:
    1,930
    Guenther Liebowitz
    Aug 14, 2003
  2. Vencz Istv?n
    Replies:
    2
    Views:
    296
  3. Eddie
    Replies:
    4
    Views:
    377
    Aaron Bertrand [SQL Server MVP]
    Aug 25, 2005
  4. Pascal Meunier

    Ruby DBI transaction support bug?

    Pascal Meunier, Apr 27, 2007, in forum: Ruby
    Replies:
    1
    Views:
    146
    Pascal Meunier
    Apr 27, 2007
  5. markspace

    JDBC transaction isolation

    markspace, May 3, 2012, in forum: Java
    Replies:
    16
    Views:
    1,248
Loading...

Share This Page