Re: deleting a row potentially referenced by many rows in another table

On Sep 29, 12:31 pm, cm <cmonthe..._at_yahoo.com> wrote:
> > Yeah, off-hand all that comes to mind is a trigger that fires on
> > DELETEs and does an EXISTS test. Since your PK and, almost certainly,
> > FK columns are indexed, the performance may be acceptable. Which RDBMS
> > are you running?
>
> > --Jeff
>
> I am running MySQL. Yes, the trigger approach was suggested to me
> separately, and probably could be made to work. I am wondering about
> concurrency issues though. Suppose the EXISTS test indicates that no
> FKs reference the applicable parent row when the check is made, but,
> before the delete of the parent row can be made, another insert finds
> the parent row already exists and references it.
>
> Thanks,
> Carl

I'm not familiar enough with mySQL to give you a definitive answer, so you may want to post your latest question to that forum. What is mySQL's concurrency model (for lack of a better word)?--is it more traditional (lock list, isolation level, etc.) like, say, DB2, or is it more like Oracle's (MVCC-like, i.e., writers never block readers, no lock list, etc.)? Or, does the answer to that question depend on which storage engine was implemented?

--Jeff Received on Wed Sep 30 2009 - 02:18:26 CEST