on 02-09-2011 12:32 PM
Hello Experts
We have a repeatative issues in JDBC sender adapter .While it is reading data from database sometimes we are facing the problem tat all the data is not read by adapter but PI read date is generated for all the records.
Some times the issue happened like PI processed first three and last three data but missed 2-3 records from the middle portion.
Please help me to know the reason for this error and how to resolve this types of error.
Thanks in advance .
Somenath
In your communication channel, set under "advanced" tab the parameter "Transaction Isolation level" to serializable.
Compare your select and update clauses. The where-clauses should be absosute identical.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello All,
Thanks for your help .We are now using the advanced configuration transaction Isolation level to serializable.
Till now no more complain received .Let's hope for the best .
Once again thank you very much for your advice .
Regards,
Somenath
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Stefan ,
Thanks a lot for your reply .
We have used the below Select and update statement
I am not expert in select query .Please suggest if the query is okay or it can be the error.
SELECT * FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL ORDER BY [Delivery_Number] ASC) AND [PI_Read_Date] IS NULL ORDER BY [Transaction_ID] ASC
UPDATE [database].[dbo].[table] SET [PI_Read_Date] = getdate() WHERE [Transaction_ID] in ( SELECT [Transaction_ID] FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL) AND [PI_Read_Date] IS NULL)
Please let me know what new features we can get if we set the the advanced parameter serializable.
Hello Navin,
We are using toad at data base side .If the lock occurs in the database side is it possible to generate tHe PI read date as the data is not read by PI?
Thank you once again for you help.
BR.
somenath
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
By setting the transaction to serializable mode.. what ever the records selected during that period of processing only will get updated ...
I.e records inserted between select and update will not be updated as they have not selected in selection statement for processing..
execute the query in database and see whether the resultset is as per your requirement or not ..and similarly the update statement...
HTH
Rajesh
> SELECT * FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL ORDER BY [Delivery_Number] ASC) AND [PI_Read_Date] IS NULL ORDER BY [Transaction_ID] ASC
>
> UPDATE [database].[dbo].[table] SET [PI_Read_Date] = getdate() WHERE [Transaction_ID] in ( SELECT [Transaction_ID] FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL) AND [PI_Read_Date] IS NULL)
select and update is different, so it can happen that the where clause in update hits more or less table entries as select.
Put the same where clause in update as is select:
UPDATE [database].[dbo].[table] SET [PI_Read_Date] = getdate() WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL ORDER BY [Delivery_Number] ASC) AND [PI_Read_Date] IS NULL
> Please let me know what new features we can get if we set the the advanced parameter serializable.
serializable means that the whole table is locked against concurrent access during the processing of JDBC adapter. So there cannot be any new entries in table between select and update clause.
Just few cents ... we dont need to check P_Read_date not null two times in the update query.
Since we use Select and Update statement in the jdbc adapter (sender) there is a possibility that data might get changed in between Select and UPdate calls to database. To avoid that we use Transaction Serializable. By doing this, jdbc adapter call is executed in a single thread for both the calls and letting other concurrent calls to wait in the queue at db level.
Hi Somenath,
This occurs due to lock occured on the data which you are trying to read.IF some other is accessing the same data or trying to read the data there there will be a lock on that perticular data and because of that you are unable to access the data.
Especially this happens if you are using TOAD.... if you open TOAD and try to access data through adapter probably you may get such situation.
Cheers!!!!
Naveen.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.