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
>
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>