James Black <[EMAIL PROTECTED]> wrote on 01/20/2005 03:08:33 PM: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > [EMAIL PROTECTED] wrote: > | You should already KNOW the parent comment of a comment when you create > | it. Don't you? Imagine the following thread: > > ~ I am trying to not have to pass in the parent id, but I may have to do > that, if need be. The problem I run into is that there may be more than > one comment to the last comment seen, and I want each comment to only > have one child, so by having it do a subquery I can ensure this to be > more likely, as it will be more atomic of an operation. > > ~ I don't know if we are going to have threaded comments yet. > > - -- > "Love is mutual self-giving that ends in self-recovery." Fulton Sheen > James Black [EMAIL PROTECTED] > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.5 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFB8A/BikQgpVn8xrARAumUAJ9Q0mOjzHk0tM+Gk0tmRqlJyQ09bgCeM3dK > t6DnKYczJQkLcBHHlOA7u0c= > =F0Dm > -----END PGP SIGNATURE-----
With the each "child" holding a "parent" id, you leave yourself the opportunity to store more than one child per parent. If you invert your definition of what goes into that field and made it a "next comment" id or "child comment" id, you will enforce by database design only one child per comment. Either way you go I don't think you can do what you are trying to do in a single statement. The error you are getting is telling you that you are attempting to access a table that is currently locked (it's locked due to the other predicate in your statement). Either your INSERT is blocking your SELECT or your SELECT is preventing the INSERT. This wouldn't happen if you were selecting and inserting to different tables but you aren't. I would suggest you rewrite your INSERT process as two or more statements (get max(), do insert) wrapped either in a LOCK/UNLOCK block or BEGIN/COMMIT block to enforce the atomicity you desire. Shawn Green Database Administrator Unimin Corporation - Spruce Pine