Need Help Determining Locks
Joel asked how to find which table and process held a lock in his VB/ADO application on Informix on SCO, as lock errors lasted minutes. Girish Punjabi suggested onstat -u, -k, -s, -g sql or syslocks. Art S. Kagel described tracing onstat -k (tblsnum, X locks first) to the owner in onstat -u, then onstat -g ses. Richard Spitz asked if a single-command script existed; no answer.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I've developed a multi-user system using VB, ADO and Informix. The Informix DB is on a SCO server. I acknowledge that locks are inherent in multi-user systems, but from time to time one particular application seems to get a record lock error for an unusual amount of time, sometimes a couple of minutes. Is there a way, from within VB or through an Informix utility, to determine what table is being locked and what process is locking it? Thanks, Joel Sent via Deja.com http://www.deja.com/ Before you buy.
Joel,
You have not mentioned the verion of Informix you are using .If the
version is > 7.x , then you have use onstat ( informix utility )
command with foll. options :
1. -u , to check users and transactions
2. -k , lock status
3. -s , latch status
4. -g sql , to check the query running .
5. -- for help.
You can also query the syslocks table .
If the version is 5.x, use tbstat command with first 3 options.
Cheers , Girish
In article <8gghop$70f$1@nnrp1.deja.com>,
jwz1@my-deja.com wrote:
> I've developed a multi-user system using VB, ADO and Informix. The
> Informix DB is on a SCO server. I acknowledge that locks are inherent
> in multi-user systems, but from time to time one particular
application
> seems to get a record lock error for an unusual amount of time,
> sometimes a couple of minutes.
>
> Is there a way, from within VB or through an Informix utility, to
> determine what table is being locked and what process is locking it?
>
> Thanks,
>
> Joel
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
--
Cheers, Girish
Sent via Deja.com http://www.deja.com/
Before you buy.
jwz1@my-deja.com wrote:
>
> I've developed a multi-user system using VB, ADO and Informix. The
> Informix DB is on a SCO server. I acknowledge that locks are inherent
> in multi-user systems, but from time to time one particular application
> seems to get a record lock error for an unusual amount of time,
> sometimes a couple of minutes.
>
> Is there a way, from within VB or through an Informix utility, to
> determine what table is being locked and what process is locking it?
The onstat utility:
onstat -k shows locks. The tblsnum column shows the partnum of the tableon which the lock is being held. Look for type X (exclusive) locks first
then if none look for type S (shared) locks. The owner column value will
correspond to the address column in the onstat -u report which will give
you the session id. Then onstat -g ses <sesid> will give you pid, user,
database, current and last SQL, client host, etc.
Art S. Kagel
"Art S. Kagel" wrote:
> The onstat utility:
> onstat -k shows locks. The tblsnum column shows the partnum of the table> on which the lock is being held. Look for type X (exclusive) locks first
> then if none look for type S (shared) locks. The owner column value will
> correspond to the address column in the onstat -u report which will give
> you the session id. Then onstat -g ses <sesid> will give you pid, user,
> database, current and last SQL, client host, etc.
Art,
thanks for reiterating on this subject. This question seems to be
coming up quite frequently, and it is indeed a common problem for
DBAs everywhere.
I wonder why it has to be so complicated to get to the relevant
information, going through several consecutive "onstat" commands.
Has anyone already written a script or some other solution that will
deliver the information through a single command only?
Regards, Richard
--
+----------------------------+---------------------------------------+
| Dr. med Richard Spitz | E-Mail: spitz@ana.med.uni-muenchen.de |
| EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 |
| Klinikum der Univ. München | FAX : +49-89-7095-6420 |
| 81366 Munich, Germany | GSM : +49-172-8933578 |
+----------------------------+---------------------------------------+