This is a snipet of an email that I recieved from a co-worker. Thought I would post this here and see if anyone could help. We are rather new at Informix and any assistance would be Greatly appreciated.

------------------------------------------

I was doing some testing to ensure that our database deadlock processing code works correctly. I have two options in a test utility to facilitate this. To run the test you have to have two copies of the utility running in different terminal sessions.

The first option selects a row from table A for update, waits for the user to allow the test to proceed and then selects a row from table B for update. The second option selects a row from table B for update, waits for the user to allow the test to proceed and then selects a row from table A for update. This should produce a deadlock condition.

I simply wanted to ensure that I really have the rows locked in the database. Hence the question of how do you view locks in the database.

In Oracle I would run this query to view the locks. This very Oracle specific. I just wanted to know how to do it in Informix.

select os_user_name "User", object_name "Object", program "Program",
machine "Machine",terminal "Terminal", to_char(sysdate, 'yyyy-mon-dd hh:mi:ss') "Time"
from v$locked_object, dba_objects, v$session
where v$locked_object.object_id = dba_objects.object_id and
v$locked_object.session_id = v$session.sid

To know if there are deadlocks, do an onstat -p.

To check for locks in the database, you can query the syslocks system table.

Be sure that you have a row lock level on your tables.