Hi Guys,

There has been an issue with our applications in develpment environment.
A Staging server is locking GAME_SERVER_STATUS table. On investigation I found that an update on this table is schedulled to be every 3 Secs. This is done by Gaming Application Server.

When I see a lock on the table there are two updates from same server. Does that mean the first update request did not finish in 3 seconds.

That keeps happening. The development team wants me to terminate the first update thread (agent) if it takes more than 2 seconds.

So, is there a way to terminate a transaction if it takes longer to execute ?


Regards

DBFinder

I think you need to fix the problem, and not the symptom. The symptom is that the update is not terminating soon enough. Killing it will not alleviate the problem. You need to determine why the update is lasting longer than necessary. There can be several reasons. It could be the network. It could be the update statement. It could be that the application is not committing the update.

Andy

Alright,
I am working on this. The question I asked is beacuse they want me to do that; which I refused to do officially. However, I want to learn, if there is any way to do that. Definitely, you may trust me that as a DBA I would not do that.
As an Application Developer I do believe that they are not using commit at the end.

Regards

There is no parameter that I can think of that could do this. You would have to write a script that constantly loops looking for an application that is holding a lock for too long, and then kill (force) that application. I see far too many potential problems from this "solution".

Andy

That's why it is not available officially. By the way the server is in STAGING envoirnment, and the manager has sent me a request to setup a cron job that will kill this thread.
I have also requested that java code to see if i can reccomend any change to it.

Thanks for your support.

That's what the "Workload monitor" is for.
http://publib.boulder.ibm.com/infoce.../c0052594.html

It can be used to assign certain resource limits to "user groups" and based on the resource comsumption the transaction could e.g. be terminated.

It is not easy to setup and I think it is an additional option in the enterprise edition that you have to pay for.
http://publib.boulder.ibm.com/infoce.../c0052600.html

Hi Andy,

The problem has been identified. I found that some connections were being dropped amidst the transactions. I sent this observation to IMS team which further analyzed network devices. They found some settings on firewall needed to be set according to server's response time.
At the same time I offered one server from my personal lab that was accepted and put to test. This server worked good with old and new firewall settings,IMS reported.
The killer loop that I created worked very nice. Any lock that persisted 20 sec on one main table was forced affectively.This table X lock was held by application server while maintaining connection pool. So this temporary solution proved life saver to other applications. This was needed as a pain killer. Please note that this kind of solution was applied to the STAGING server on developers' demand because the server belonged to developers solely.

This will be tested again when I will put original server back, somewhere next week.

Thanks everybody for support.

DBFinder