Re: SQL locks entire table on INSERT?

[Alexei Pytel <pytel@xxxxxxxxxx>]

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