Re: SQL locks entire table on INSERT?

Start from this place in SQL reference:
 
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmstisol.htm#HDRISOL
this is a general discussion of isolation level, how it is set etc...

    Alexei Pytel

always speaking for myself




"Patrick Townsend" <patownsend@xxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/12/2003 11:47 PM
Please respond to Midrange Systems Technical Discussion

 
        To:     "Midrange Systems Technical Discussion" 
<midrange-l@xxxxxxxxxxxx>
        cc:     <midrange-l@xxxxxxxxxxxx>, <midrange-l-bounces@xxxxxxxxxxxx>
        Subject:        Re: SQL locks entire table on INSERT?



Hi Alex,

The application must be using the default isolation level. Do you know 
what
the default isolation level is for AS/400? Do you know how to change the
isolation level? I can find discussions of isolation level on the IBM web
site, but I can't see what API to call to change it.

TIA,
Patrick

----- Original Message -----
From: "Alexei Pytel" <pytel@xxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Cc: <midrange-l@xxxxxxxxxxxx>; <midrange-l-bounces@xxxxxxxxxxxx>
Sent: Monday, May 12, 2003 12:26 PM
Subject: Re: SQL locks entire table on INSERT?


> If a certain isolation level was requested, SQL places an exclusive lock
> on the table.
>
>     Alexei Pytel
> always speaking for myself
>
>
>
>
>
> "Patrick Townsend" <patownsend@xxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 05/10/2003 10:49 PM
> Please respond to Midrange Systems Technical Discussion
>
>
>         To:     <midrange-l@xxxxxxxxxxxx>
>         cc:
>         Subject:        SQL locks entire table on INSERT?
>
>
>
> Here's an odd one. I have a C application on the AS/400 that uses the 
SQL
> CLI interface to insert records into a database. The application uses
> commitment control. For performance reasons the application does the
> SQLConnect and binds parameters at start up, and then sits on a data 
queue
> waiting for work to do. When there is work to do data is formatted and
> INSERTed into the database. The number of records inserted varies, but 
is
> usually not many (less than a dozen or so). From watching the locks on 
the
> files into which records are inserted, and from watching the performance
> of
> the jobs, it looks like only one process can insert data at a time. That
> is,
> it seems like a process has an exclusive lock on the entire table during
> the
> update process. Other processes that may be running simultaneously seem 
to
> go into lock-wait until they can get an exclusive lock. Almost like a
> single-user database! I can understand that the records that are 
inserted
> under commitment control would be locked, but I can't understand why the
> entire table would be locked. Any thoughts on this would be appreciated.
>
> Patrick
>
>
>