CODEWITHSUNDEEP
jakarta-eesql-server-2005transactions
prog_debugger
prog_debugger

Reputation: 23

Count mismatch in sql browser and java JDBC, due to high transaction

I'm using SQL Server 2005. There is more concurrent access on this table1.

When I execute the following query in SQL Browser I'm getting 2000 records, but when the same query is executed with java, no of records r varied. 

EX: 1st execution --> 2000 records,
    2nd execution --> 2005 records, 
    3rd execution --> 1990 records


 select 
     REGISTRATION_NO as TOTAL_ASSETS_COUNT 
 from 
     Contable1 a 
 inner join 
     table2 b on a.REGISTRATION_NO = b.Registration_no 
              and a.System_id = b.System_id 
 inner join 
     table3 c on b.Registration_no = c.VehicleNo 
              and b.System_id = c.System_id 
 where 
     a.CLIENTID = ? and a.System_id = ? and b.User_id=?

I'm using isolation level as READ COMMITTED.

Due to high transaction on table1 the count is varied.

Please help to resolve why the count is varied in java.

NOTE: Registration_No is the primary key.

Thanks in advance.

Upvotes: 2

Views: 143

Answers (1)

shivadarshan
shivadarshan

Reputation: 946

As you said due to high transaction, there is variation in count.

There may be following possibilities which varies count:

1) There may be some transactions happening in the background before you read the data. or

2) There may be some duplicate data in table you join.

3) As @greet said there may be change in values of other table where you join with.

As i would request you to check the queries properly. if you claim that above are not the possibilities. i request you to provide more details about table schema.

Upvotes: 1

Related Questions