 | |
Seeing uncommitted rows in version 11 v. spurious constraint error?
sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Seeing uncommitted rows in version 11 v. spurious constraint error? -
03-30-2009
, 03:28 AM
|
To clarify somewhat (I hope)...I think it might be that there are no matching items (i.e. the rogue items I'm trying to find) and there are no wrong foreign key elements, and in fact it is a spurious error message. However, where it is coming from is another question. I have worked around this for the time being by using a "brute force" method of dropping and recreating all the foreign key definitions in the database prior to running this large process and this prevents the error from occurring. I don't know why. My money is on that there is an underlying bug with some sort of optimisation of foreign keys, and once you drop the foreign keys and get back to square one, the problem disappears. Anyway, I'm able to get by now, so I guess I'll have to leave this aside for now. Thanks for the comments. cheers, Steve M Ok, thanks. I will try and come up with a reproducible scenario, or if I find the reason I'll post the result. So this is sounding much less like an issue with seeing uncommitted row modifications with an isolation level 0 read transaction, and instead looks like something related to row locking for your application. The relevant option setting for your application that I can see is the wait_for_commit connection option. You can also specify CHECK ON COMMIT for any foreign key definition, so that that specific RI constraint isn't verified until each and every transaction commits. If specifying this option or altering the RI constraints themselves don't do the trick, we're going to need a real reproducible before we get much further. Glenn steve m wrote: Ok, I'm sure it's something I'm doing but...This is now morphing more into the area of "spurious", which I didn't really expect, but the thing is this: I can run the exact same process and get different results as follows (but I don't know why): 1) I run my program with the big transaction with loads of deletes and some inserts back of the same items, by the way (it deletes a load of things and then recreates mostly the same but with some differences) and then I get the error "no primary key for foreign key A in table B". This keeps on happening (dozens of times, trying different database options) till I delete the foreign key definition and recreate it. 2) Now I run the process and get another different error (moved on, sort of thing) "no primary key for foreign key B in table C". So by now I've got the trick of it, so I delete the foreign key definition B in table C and recreate it apparently identically. 3) Now my process completes without any errors. Sort of good, but... 4) After some data entries etc (possibly for many sessions, completed and closed all ok) I come back and try to re-run this process and I'm back to square one with the foreign key messages and the only way around being to delete and recreate the definitions. 5) Another thing that happens is that my transaction rollback command part of my error handling is not working. It doesn't produce any error message, but when I think I'm ready to do some more data entries or whatever, lo and behold, the earlier data integrity error message keeps reappearing, as it hasn't taken the rollback seemingly. I know it's a saga, and perhaps a little vague, but I'm hoping some bells will ring and somebody will think of what this syndrome might be down to. I think I've tried every vaguely relevant database option to see if it would have any effect but none have. After each test I've gone back to the default, just so as not to get things muddled up. I've spent all day on this so far and will continue to think of more things to try, but if there's an EBF or something that might have some relevance of course I will try that. I'm kind of out of ideas now, for the moment. (Other than the painful workaround of deleting and recreating foreign key definitions.) :-(. Both the underlying data store, and the locking model, changed completely with version 10 (as you must know, a version 10 server cannot run a version 8 database for that reason). It is unsurprising to me that the behaviour you're seeing running at isolation level 0 in an attempt to "see" uncommitted changes is different as a consequence. Precisely *why* you are seeing different behaviour will depend on precisely what both the updating transaction and the querying transaction are doing. If you can be more precise in your description of what you are experiencing, I may be able to give you precise reasons as to why you are seeing these changes. One thing to keep in mind, of course, is that SQL Anywhere makes absolutely no guarantees of this behaviour (ie "seeing" uncommitted updates/deletes/inserts) when running at isolation level 0. Glenn steve m wrote: It's 11.0.0.1264 and was 8.03 before (not sure the build right now). I put more about it in a reply above just now. Ta. What SQL Anywhere versions are you comparing? Glenn steve m wrote: Hi, I used to be able to stop my application program before executing rollback when there was a problem with referential integrity and use interactive SQL to find the invalid uncommitted rows. Since I upgraded to version 11 and using interactive SQL from the new Sybase Central, by default I don't seem to get the uncommitted rows returned, which makes it harder to debug. If it's not that, then I suspect I might even be getting spurious referential integrity errors, as I can't find any rows that seem to violate the rules. For example it will say that "there is no primary key for foreign key so-and-so, cannot commit" and I pause my program before rollback, but I can't select any rows that are in violation. Does this ring any bells with anybody? I doubt I could give a simple example to replicate it, as it happens at the end of a very large complex transaction. cheers, Steve M. www.silverlink.co.uk Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...sqlanywhere/te c h ni ca lsupport Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...lanywhere/tech n i ca lsupport Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...nywhere/techni c a lsupport |
#12
| |||
| |||
|
|
To clarify somewhat (I hope)...I think it might be that there are no matching items (i.e. the rogue items I'm trying to find) and there are no wrong foreign key elements, and in fact it is a spurious error message. However, where it is coming from is another question. I have worked around this for the time being by using a "brute force" method of dropping and recreating all the foreign key definitions in the database prior to running this large process and this prevents the error from occurring. I don't know why. My money is on that there is an underlying bug with some sort of optimisation of foreign keys, and once you drop the foreign keys and get back to square one, the problem disappears. Anyway, I'm able to get by now, so I guess I'll have to leave this aside for now. Thanks for the comments. cheers, Steve M Ok, thanks. I will try and come up with a reproducible scenario, or if I find the reason I'll post the result. So this is sounding much less like an issue with seeing uncommitted row modifications with an isolation level 0 read transaction, and instead looks like something related to row locking for your application. The relevant option setting for your application that I can see is the wait_for_commit connection option. You can also specify CHECK ON COMMIT for any foreign key definition, so that that specific RI constraint isn't verified until each and every transaction commits. If specifying this option or altering the RI constraints themselves don't do the trick, we're going to need a real reproducible before we get much further. Glenn steve m wrote: Ok, I'm sure it's something I'm doing but...This is now morphing more into the area of "spurious", which I didn't really expect, but the thing is this: I can run the exact same process and get different results as follows (but I don't know why): 1) I run my program with the big transaction with loads of deletes and some inserts back of the same items, by the way (it deletes a load of things and then recreates mostly the same but with some differences) and then I get the error "no primary key for foreign key A in table B". This keeps on happening (dozens of times, trying different database options) till I delete the foreign key definition and recreate it. 2) Now I run the process and get another different error (moved on, sort of thing) "no primary key for foreign key B in table C". So by now I've got the trick of it, so I delete the foreign key definition B in table C and recreate it apparently identically. 3) Now my process completes without any errors. Sort of good, but... 4) After some data entries etc (possibly for many sessions, completed and closed all ok) I come back and try to re-run this process and I'm back to square one with the foreign key messages and the only way around being to delete and recreate the definitions. 5) Another thing that happens is that my transaction rollback command part of my error handling is not working. It doesn't produce any error message, but when I think I'm ready to do some more data entries or whatever, lo and behold, the earlier data integrity error message keeps reappearing, as it hasn't taken the rollback seemingly. I know it's a saga, and perhaps a little vague, but I'm hoping some bells will ring and somebody will think of what this syndrome might be down to. I think I've tried every vaguely relevant database option to see if it would have any effect but none have. After each test I've gone back to the default, just so as not to get things muddled up. I've spent all day on this so far and will continue to think of more things to try, but if there's an EBF or something that might have some relevance of course I will try that. I'm kind of out of ideas now, for the moment. (Other than the painful workaround of deleting and recreating foreign key definitions.) :-(. Both the underlying data store, and the locking model, changed completely with version 10 (as you must know, a version 10 server cannot run a version 8 database for that reason). It is unsurprising to me that the behaviour you're seeing running at isolation level 0 in an attempt to "see" uncommitted changes is different as a consequence. Precisely *why* you are seeing different behaviour will depend on precisely what both the updating transaction and the querying transaction are doing. If you can be more precise in your description of what you are experiencing, I may be able to give you precise reasons as to why you are seeing these changes. One thing to keep in mind, of course, is that SQL Anywhere makes absolutely no guarantees of this behaviour (ie "seeing" uncommitted updates/deletes/inserts) when running at isolation level 0. Glenn steve m wrote: It's 11.0.0.1264 and was 8.03 before (not sure the build right now). I put more about it in a reply above just now. Ta. What SQL Anywhere versions are you comparing? Glenn steve m wrote: Hi, I used to be able to stop my application program before executing rollback when there was a problem with referential integrity and use interactive SQL to find the invalid uncommitted rows. Since I upgraded to version 11 and using interactive SQL from the new Sybase Central, by default I don't seem to get the uncommitted rows returned, which makes it harder to debug. If it's not that, then I suspect I might even be getting spurious referential integrity errors, as I can't find any rows that seem to violate the rules. For example it will say that "there is no primary key for foreign key so-and-so, cannot commit" and I pause my program before rollback, but I can't select any rows that are in violation. Does this ring any bells with anybody? I doubt I could give a simple example to replicate it, as it happens at the end of a very large complex transaction. cheers, Steve M. www.silverlink.co.uk Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...sqlanywhere/te c h ni ca lsupport Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...lanywhere/tech n i ca lsupport Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...nywhere/techni c a lsupport |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.