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:
Post a Comment