Re: How to find out why a table is locked? (2nd try)

Mode Ansi enforces repeatable read. That means that if a transaction reads a row it places a shared lock on the row. That shared lock will prevent the row from being altered or deleted by another thread. I suspect that is what has happened. "Sven C. Koehler" wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote: > > Oh come now. You have GOT to give us something more to work with. > > Ok. I gathered some more information. :-) > > I am using Informix Dynamic Server Version 7.30.UC5 on sparc-solaris 2.7. > My application runs on Redhat linux 6.1 (intel) and accesses informix via odbc. > > The application's database is created "with log mode ansi". > > The application itself consists of three processes: One welcomes incoming > connections, and hands them on to one of the other two processes (called sql > processes from now on), which generate sql statements based on the user's > requests. > > The two sqlprocesses "set lock mode to wait", and use transactions when writing > to the database. > > The infinite lock occurs when a user ends his session and connects again, being > now connected to the other of the two sqlprocesses. > > When I use onstat on the informix host to examine the situation, the > output looks like the following: > > % onstat -g sql> Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers > 34 - testdb RR Wait 0 0 9.15 > 33 DELETE testdb RR Wait 0 0 9.15 > > Using subsequent onstat -g sql commands, I see that session 34 belongs > to the sqlprocess formerly executing the user's requests, and session 35 > belongs to the process that the user is connected now. > > % onstat -g sql 34> Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers > 34 - testdb RR Wait 0 0 9.15 > > Last parsed SQL statement : > SELECT DISTINCT users.user_id, users.default_grp_id, users.owner_user_id, > users.user_email, users.user_isgroup, users.user_locked, users.user_login, > users.user_password, users.user_realname FROM users WHERE > user_login='root'> > % onstat -g sql 33> Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers > 33 DELETE testdb RR Wait 0 0 9.15 > > Current SQL statement : > DELETE FROM users WHERE user_id=156384> > Last parsed SQL statement : > DELETE FROM users WHERE user_id=156384> > "onstat -k" output looks like this (I don't actually understand the output): > % onstat -k> Locks > address wtlist owner lklist type tblsnum rowid key#/bsiz > a01fd38 0 a122104 a020218 HDR+X 300022 200 0 > a01fd6c 0 a12179c a020114 HDR+IS 300028 0 0 > a01fda0 0 a122104 0 HDR+S 100002 215 0 > a01fdd4 0 a122104 a0203ec S 300028 500 0 > a01fe08 0 a122104 a01fdd4 HDR+X 300028 600 0 > a01fe70 0 a12179c a0201e4 HDR+S 300028 500 0 > a01ff0c 0 a122104 a01fe08 HDR+X 300028 200 2 > a01ff74 0 a122104 a01fda0 HDR+SIX 300022 0 0 > a01ffa8 0 a122104 a01fd38 HDR+X 300022 1b200 1 > a0200e0 0 a122104 a01ff0c HDR+X 300028 300 3 > a020114 0 a12179c 0 S 100002 215 0 > a02017c 0 a122104 a01ff74 IX 300028 0 0 > a0201e4 a122104 a12179c a01fd6c HDR+S 300028 400 4 > a020218 0 a122104 a02017c S 300028 400 0 > a0203ec 0 a122104 a01ffa8 HDR+X 300028 100 1 > 15 active, 2000 total, 2048 hash buckets > > Having read "Informix's guide to sql", > the section to "set lock mode wait" states: > > "If you do not specify an upper limit and the > process that placed the lock somehow fails to release it, suspended > processes could wait indefinitely." > > I think that's the case here, but I don't know what session 34 does that > requires it to lock the database. As you can see, the last sql command was > a select, which is read only, and hence should not require a lock (when writing > to the database, the last sql command is usually a "commit"). > When I kill the now free sqlprocess, the locked sqlprocess resumes its work, > which also suggests that informix somewhat thinks that it is locking something. > > Sybase and oracle behave well in the same situation, so I think the actual > code should be correct. > > I greatly appreciate any hints to solve the problem. > > Thank you in advance, > > Sven C. Koehler > -- > email: schween at snafu de

Madison Pruet <mpruet@home.com> writes: >Mode Ansi enforces repeatable read. That means that if a transaction reads a row >it places a shared lock on the row. That shared lock will prevent the row from >being altered or deleted by another thread. I suspect that is what has happened. > Did I get you right? If a process reads a row from the database outside a transaction, it remains locked beyond the execution of the read statement (eg. select from)? That sounds strange to me... However, do you have any hints to get informix only locking rows when an actual statement is executed, so the lock will be removed after the read operation completed? Best regards, Sven Koehler -- email: schween at snafu de

Use commited reads (the default). The shared locks should be released when the transaction holding the locks is committed. If that is not happening, then please contact tech support. Just an FYI --- ANSI databases always create a transaction, so you might have an open transaction when you are issueing your delete. "Sven C. Koehler" wrote: > Madison Pruet <mpruet@home.com> writes: > > >Mode Ansi enforces repeatable read. That means that if a transaction reads a row > >it places a shared lock on the row. That shared lock will prevent the row from > >being altered or deleted by another thread. I suspect that is what has happened. > > > > Did I get you right? If a process reads a row from the database > outside a transaction, it remains locked beyond the execution of the > read statement (eg. select from)? That sounds strange to me... > However, do you have any hints to get informix only locking rows when an > actual statement is executed, so the lock will be removed after the read > operation completed? > > Best regards, > > Sven Koehler > > -- > email: schween at snafu de -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================

Madison Pruet <mpruet@informix.com> writes: >Use commited reads (the default). The shared locks should be released when the >transaction holding the locks is committed. If that is not happening, then please >contact tech support. Just an FYI --- ANSI databases always create a transaction, so >you might have an open transaction when you are issueing your delete. > > [...] Thank you very much! I set now "transaction isolation level read committed" at the beginning of each session, and everything works fine. :-) Thanks also to everyone else who gave hints! Best regards, Sven C. Koehler -- email: schween at snafu de