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

"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 sqlSess 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 34Sess 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 33Sess 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 -kLocks 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

In article <8lhgga$4fv0i$1@fu-berlin.de>, Sven C. Koehler <invalid@nospam.com> writes > >"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 Repeatable Read isolation level! This means every record read gets a shared lock put on it. > >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 -- David Williams