Only change to your query statements was that in Session 2, I should not be issuing a begin work; statement.
One more small question, is it possible that i use only "select * from bla" in the second session and it still will hang!! that means, I do not want to use the statement "set lock mode to wait" in my second session!!
You need the 'SET LOCK MODE' clause. Here's the explanation:
The default behavior for a database server is to immediately return an error to a process when an SQL request is blocked by an existing lock. If you prefer that the database server wait for the lock to be released, you can use the SET LOCK MODE statement to specify how long the database server should wait for the lock to be released. If the lock is not released within the period you specify, the operation fails and an error is returned to the requesting process.
Use care when you set the lock mode to WAIT without specifying a maximum wait interval. If you do not specify a wait interval for SET LOCK MODE, your process could, theoretically, wait forever. For example: SET LOCK MODE TO WAIT;
Even when you would simulate a deadlock, this would be detected and aborted. It is abnormal database behaviour to just 'hang' ...
What you coul ddo is simulate a hang. If you define a cursor in your client application, with an isolation level set to 'repeatable read' and you don't do a next fetch. This is actually just an illustration of bad programming, but it could be used in your test scenario. You will need a client application i.e. 4GL or esql/c to test this. dbaccess is not enough I'm afraid.
sess2 (in dbaccess):
create table bla1(col1 serial);
the sess2 will hang until you issue an onmode -c unblock in your first session. This only works with updates since the checkpoint is blocking the server. Be aware that when your server is blocked no updates/inserts/deletes/DDL will be executed. It is not a 'select' as you requested, but it works too.
You can crash the server with 'onmode -I'.
This command will trap an error and create an AF file.
There is a way to make the server 'hang' while he is creating the AF file.
Let me see if I can find it...
Before you startup the engine, set the environment variable AFDEBUG=1
Startup the engine and open dbaccess, connect to the database.
In another session, check with 'onstat -g sql', to find out th esessid of the dbaccess session
You can trap an error with onmode -I <err> <sessid>
It doesn't matter what error you want to trap. The one I always use is 206 (table not found).
set f.ex. onmode -I 206 76
This command will generate an af file in /tmp and send various log records to the message log. (onstat -m)
Now because you set the AFDEBUG, th eengine will freeze to gather additional diagnostics.
If you can't set AFDEBUG, you can use 'onmode -A 1' to turn it on and 'onmode -A 0' to turn it back off.