Tuesday, December 1, 2009

SQL Sequence Cache - Missing ids

Carefully decide your sql sequence caching strategy especially in case of multiple connections accessing the table. We often use db as a storage for Producer Consumer scenario, like below


App1 ---(inserting)---> TableA <---(querying)--- App2 


TableA has a before-insert trigger which creates primary key using the sequence. 


Now for consumer application i.e. App2, I was using a query 
select * from TableA where id > :lastReadId
where :lastReadId is obviously the id which App2 accessed on last query. 


Run both apps and you'll see a problem of missing ids, i.e. some ids are missed during insertion and consequently data is not consumed completely. A peek will show that this problem arises when we use cached sequence, so change it no-cache and it'll work fine.





No comments: