Re: SQL locks entire table on INSERT? (more)

Alex and Vern,

I've impemented a call to the SQL CLI function SQLSetConnectAttr to override
the default isolation level to SQL_TXN_READ_UNCOMMITTED. This should allow
dirty reads, etc.  The code, called after the SQLConnect() looks like this:

SQLINTEGER attr;
attr = (SQLINTEGER)SQL_TXN_READ_UNCOMMITTED;
rc = SQLSetConnectAttr(hdbc,
                      (SQLINTEGER)SQL_TXN_ISOLATION,
                      (SQLPOINTER)&attr,
                      (SQLINTEGER)sizeof(attr));

However, I still have file locks. Here is an example from the WRKOBJLK
output:

*************************************************
                             Work with Object Locks
                                                             System:
S105PPMM
 Object:   INVDTL         Library:   ALLXML100      Type:   *FILE-PHY

 Type options, press Enter.
   4=End job   5=Work with job   8=Work with job locks

 Opt   Job          User         Lock      Status          Scope     Thread
       QSQSRVR      QUSER        *SHRRD     HELD           *JOB
       TESTPERF     TOWNSEND     *EXCL      WAIT           *THREAD
00000005
       TESTPERF     TOWNSEND     *EXCL      WAIT           *THREAD
00000007
       TESTPERF     TOWNSEND     *EXCL      WAIT           *THREAD
00000004



Bottom
 F3=Exit   F5=Refresh   F6=Work with member locks   F12=Cancel

***************************************************

Based on what I read in the IBM documentation I would not have expected to
see the exclusive locks on the database.

Any thoughts?

Patrick

----- Original Message -----
From: "Patrick Townsend" <patownsend@xxxxxxxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Saturday, May 10, 2003 8:49 PM
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.
>
>