× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.