User:Ashish.zan

Connection locking problem

Connection locking - One table is being used by other resource for reading purpose and same table is being used by different resource for writing or updating purpose. Same time both resources want to use same table due to which there may be chances for connection locking.

Database level

Use Date field instead of using String for date kind of records.

Java api level

Use prepared statement instead of using statement.

Query level

Try to form good join from filtering left to right.

Logic level

Always close the resultset and connection after using it immediately.

Causes

Bad design query

Forget connection closing

Using synchronize blocks

Using resultset so long

Using autocommit(false)mostly

Using rollbacks wrong way

Bad query design

Use of stage 2 predicate

Sollution

Create Index in long data tables.

Put resultset data in collection and free it as soon as possible.

Never use methods and system.out.println while using resultsets.

Try to use indexed predicate 1 stage query or at least predicate 1 not indexed but not predicate 2 Ex. predicate 1(<>) predicate 2(!=)

Make nested query and joins accordingly which gives rows very less that check should be used first. Ex. in usermaster table user_id(primary),user_name is given.

Bad query : select * from user_master where user_name='abc' and user_id=101.

Good query : select * from user_master where user_id=101 and user_name='abc'.

first query will take more time because 'and' priority is from left to right and in bad query first where clause check 'user_name' gives more rows but in good query 'user_id' it will give only one row so futher and processing would be minimise.The more temporary rows in the processing of first and will be there the more time will be taken by the second check in where clause. Category:Connection locking problem