Re: Do SELECT statements create any locks on a 5.X OnLine Engine

Todd Gill in <4hi2fn$2om@lynx.dac.neu.edu> wrote: > > I've read the FAQ and documentation, but can't seem to find an answer to my > (naive?) question. > > I am writing an application that I don't want to interfere with other > applications accessing (doing update/inserts) the same database. Will > declaring a cursor with hold for a query select * from some_table create > any locks that the other processes might contend with? The database is > using logging if that matters. I am using ESQL/C against a 5.X OnLine > engine. > > Thanks for any help, > Todd Gill > tgill@lynx.dac.neu.edu The answer depends on two factors: 1. Whether the SELECT statement is performed within trasaction (It's ALWAYS true for the database with LOG MODE ANSI) or not. 2. What the current ISOLATION levels of the applications accessing the same database are. So, for the database with regular (both unbuffered and buffered) logging and default for this case isolation level (Committed Read) the SELECT * from some_table statement will NOT lock the table "some_table". However, for the ANSI database (default isolation level is Repeatable Read) any SELECT statement is performed within transaction and it always WILL place EXCLUSIVE lock on row (or page, depending on the LOCK MODE) of the table "some_table". In the last case you should declare SELECT cursors with HOLD and issue COMMIT WORK inside of FETCH loop after each SELECT to permit other applications to access the same table "some_table". Take care, Pavel <pavel@cso.spb.su>