[JBoss-dev] Oracle, table locks and CMP2 incompatibility
Hallo db gurus, we have for some time experienced nasty timeouts on oracle without jboss deadlock detection (or oracle deadlock detection) kicking in. We might have found the solution today, allthough this is not verified, perhaps only time will show. (...or some db guru will tell me that my following explanation is severly misguided.) The EJB locking is based on entity locks. If you have an underlying database which does locking too, it has to use row level locking, (as opposed to table level locking) or else jboss detect deadlocks on the database, as the check which detects deadlocks is based on a graph representing locks on entity (row) level. But there are circumstances, under which oracle does not use row level locking even if with row level locking configured to always. This happens when a table TableA has a dependent table TableB in a 1:n relationship (i.e. TableB has a foreign key pointing to TableA.) If a row of TableA gets updated, oracle tries to lock all entries in TableB with matching foreign key. Here comes the vital point: If there is no index on the foreign key columns of TableB, oracle does a shared lock on _table_ level. This is probably because otherwise oracle would need to do a full table scan on the fly to find the matching rows in TableB. So if you configure jboss to generate the foreign key constraints you are walking on thin ice; you have to generate indices on the foreign key columns by hand, or else this effect might hount you. Solution: 1) [workaround] disable foreign key (or their generation), and let CMP2 itself take care of the constraints or 2) implement generation of foreign key indices in jboss. (I'm not fully convinced that this would suffice; jboss deadlock detection would have to lock the same entities and avoid race conditions). Does this sound reasonable to you? Regards, Michael Bartmann --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
RE: [JBoss-dev] Oracle, table locks and CMP2 incompatibility
Check this link. http://asktom.oracle.com/pls/ask/f?p=4950:8:1513219::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:292016138754,%7Bprimary%7D%20and%20%7Bkey%7D%20and%20%7Bupdate%7D%20and%20%7Btable%7D%20and%20%7Block%7D Dan -Original Message- From: Michael Bartmann [mailto:michael.bartmann;lisytec.de] Sent: Monday, October 28, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: [JBoss-dev] Oracle, table locks and CMP2 incompatibility Hallo db gurus, we have for some time experienced nasty timeouts on oracle without jboss deadlock detection (or oracle deadlock detection) kicking in. We might have found the solution today, allthough this is not verified, perhaps only time will show. (...or some db guru will tell me that my following explanation is severly misguided.) The EJB locking is based on entity locks. If you have an underlying database which does locking too, it has to use row level locking, (as opposed to table level locking) or else jboss detect deadlocks on the database, as the check which detects deadlocks is based on a graph representing locks on entity (row) level. But there are circumstances, under which oracle does not use row level locking even if with row level locking configured to always. This happens when a table TableA has a dependent table TableB in a 1:n relationship (i.e. TableB has a foreign key pointing to TableA.) If a row of TableA gets updated, oracle tries to lock all entries in TableB with matching foreign key. Here comes the vital point: If there is no index on the foreign key columns of TableB, oracle does a shared lock on _table_ level. This is probably because otherwise oracle would need to do a full table scan on the fly to find the matching rows in TableB. So if you configure jboss to generate the foreign key constraints you are walking on thin ice; you have to generate indices on the foreign key columns by hand, or else this effect might hount you. Solution: 1) [workaround] disable foreign key (or their generation), and let CMP2 itself take care of the constraints or 2) implement generation of foreign key indices in jboss. (I'm not fully convinced that this would suffice; jboss deadlock detection would have to lock the same entities and avoid race conditions). Does this sound reasonable to you? Regards, Michael Bartmann --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
Re: [JBoss-dev] Oracle, table locks and CMP2 incompatibility
Catalin Teodorescu wrote: Check this link. http://asktom.oracle.com/pls/ask/f?p=4950:8:1513219::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:292016138754,%7Bprimary%7D%20and%20%7Bkey%7D%20and%20%7Bupdate%7D%20and%20%7Btable%7D%20and%20%7Block%7D Dan -Original Message- From: Michael Bartmann [mailto:michael.bartmann;lisytec.de] Sent: Monday, October 28, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: [JBoss-dev] Oracle, table locks and CMP2 incompatibility Hallo db gurus, we have for some time experienced nasty timeouts on oracle without jboss deadlock detection (or oracle deadlock detection) kicking in. We might have found the solution today, allthough this is not verified, perhaps only time will show. (...or some db guru will tell me that my following explanation is severly misguided.) The EJB locking is based on entity locks. If you have an underlying database which does locking too, it has to use row level locking, (as opposed to table level locking) or else jboss detect deadlocks on the database, as the check which detects deadlocks is based on a graph representing locks on entity (row) level. But there are circumstances, under which oracle does not use row level locking even if with row level locking configured to always. This happens when a table TableA has a dependent table TableB in a 1:n relationship (i.e. TableB has a foreign key pointing to TableA.) If a row of TableA gets updated, oracle tries to lock all entries in TableB with matching foreign key. Here comes the vital point: If there is no index on the foreign key columns of TableB, oracle does a shared lock on _table_ level. This is probably because otherwise oracle would need to do a full table scan on the fly to find the matching rows in TableB. So if you configure jboss to generate the foreign key constraints you are walking on thin ice; you have to generate indices on the foreign key columns by hand, or else this effect might hount you. Solution: 1) [workaround] disable foreign key (or their generation), and let CMP2 itself take care of the constraints or 2) implement generation of foreign key indices in jboss. (I'm not fully convinced that this would suffice; jboss deadlock detection would have to lock the same entities and avoid race conditions). Even ignoring the table-lock escalation issue, this is what should happen. In fact, I'm a little surprised that Oracle doesn't just create the indices: I believe PostgreSQL does. Does this sound reasonable to you? Regards, Michael Bartmann --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
Re: [JBoss-dev] Oracle, table locks and CMP2 incompatibility
Hmmm.. if I understand this paper right, it would mean that the child table is only locked if: a) one deletes records of the parent table or b) if one changes the pk value in the parent table. which would not not be as bad as I thought when reading other sources, which imply that a simple update on the parent table would suffice to lock the associated child rows. Regards, Michael Bartmann Catalin Teodorescu wrote: Check this link. http://asktom.oracle.com/pls/ask/f?p=4950:8:1513219::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:292016138754,%7Bprimary%7D%20and%20%7Bkey%7D%20and%20%7Bupdate%7D%20and%20%7Btable%7D%20and%20%7Block%7D Dan -Original Message- From: Michael Bartmann [mailto:michael.bartmann;lisytec.de] Sent: Monday, October 28, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: [JBoss-dev] Oracle, table locks and CMP2 incompatibility Hallo db gurus, we have for some time experienced nasty timeouts on oracle without jboss deadlock detection (or oracle deadlock detection) kicking in. We might have found the solution today, allthough this is not verified, perhaps only time will show. (...or some db guru will tell me that my following explanation is severly misguided.) The EJB locking is based on entity locks. If you have an underlying database which does locking too, it has to use row level locking, (as opposed to table level locking) or else jboss detect deadlocks on the database, as the check which detects deadlocks is based on a graph representing locks on entity (row) level. But there are circumstances, under which oracle does not use row level locking even if with row level locking configured to always. This happens when a table TableA has a dependent table TableB in a 1:n relationship (i.e. TableB has a foreign key pointing to TableA.) If a row of TableA gets updated, oracle tries to lock all entries in TableB with matching foreign key. Here comes the vital point: If there is no index on the foreign key columns of TableB, oracle does a shared lock on _table_ level. This is probably because otherwise oracle would need to do a full table scan on the fly to find the matching rows in TableB. So if you configure jboss to generate the foreign key constraints you are walking on thin ice; you have to generate indices on the foreign key columns by hand, or else this effect might hount you. Solution: 1) [workaround] disable foreign key (or their generation), and let CMP2 itself take care of the constraints or 2) implement generation of foreign key indices in jboss. (I'm not fully convinced that this would suffice; jboss deadlock detection would have to lock the same entities and avoid race conditions). Does this sound reasonable to you? Regards, Michael Bartmann --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
RE: [JBoss-dev] Oracle, table locks and CMP2 incompatibility
Check out also the Oracle doc for the version you use. There are some improvements in 9i. Dan -Original Message- From: Michael Bartmann [mailto:michael.bartmann;lisytec.de] Sent: Monday, October 28, 2002 13:41 PM To: [EMAIL PROTECTED] Subject: Re: [JBoss-dev] Oracle, table locks and CMP2 incompatibility Hmmm.. if I understand this paper right, it would mean that the child table is only locked if: a) one deletes records of the parent table or b) if one changes the pk value in the parent table. which would not not be as bad as I thought when reading other sources, which imply that a simple update on the parent table would suffice to lock the associated child rows. Regards, Michael Bartmann Catalin Teodorescu wrote: Check this link. http://asktom.oracle.com/pls/ask/f?p=4950:8:1513219::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:292016138754,%7Bprimary%7D%20and%20%7Bkey%7D%20and%20%7Bupdate%7D%20and%20%7Btable%7D%20and%20%7Block%7D Dan -Original Message- From: Michael Bartmann [mailto:michael.bartmann;lisytec.de] Sent: Monday, October 28, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: [JBoss-dev] Oracle, table locks and CMP2 incompatibility Hallo db gurus, we have for some time experienced nasty timeouts on oracle without jboss deadlock detection (or oracle deadlock detection) kicking in. We might have found the solution today, allthough this is not verified, perhaps only time will show. (...or some db guru will tell me that my following explanation is severly misguided.) The EJB locking is based on entity locks. If you have an underlying database which does locking too, it has to use row level locking, (as opposed to table level locking) or else jboss detect deadlocks on the database, as the check which detects deadlocks is based on a graph representing locks on entity (row) level. But there are circumstances, under which oracle does not use row level locking even if with row level locking configured to always. This happens when a table TableA has a dependent table TableB in a 1:n relationship (i.e. TableB has a foreign key pointing to TableA.) If a row of TableA gets updated, oracle tries to lock all entries in TableB with matching foreign key. Here comes the vital point: If there is no index on the foreign key columns of TableB, oracle does a shared lock on _table_ level. This is probably because otherwise oracle would need to do a full table scan on the fly to find the matching rows in TableB. So if you configure jboss to generate the foreign key constraints you are walking on thin ice; you have to generate indices on the foreign key columns by hand, or else this effect might hount you. Solution: 1) [workaround] disable foreign key (or their generation), and let CMP2 itself take care of the constraints or 2) implement generation of foreign key indices in jboss. (I'm not fully convinced that this would suffice; jboss deadlock detection would have to lock the same entities and avoid race conditions). Does this sound reasonable to you? Regards, Michael Bartmann --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
RE: [JBoss-dev] Oracle, table locks and CMP2 incompatibility
danch wrote: Even ignoring the table-lock escalation issue, this is what should happen. In fact, I'm a little surprised that Oracle doesn't just create the indices: I believe PostgreSQL does. I believe it's because they want to allow a DBA to configure the index in a specific way e.g. in a specific tablespace, with additional columns for index-only joins, as part of the PK (leading to CMR problems :-( ), and so on. I don't think MS-SQL creates an index either (although it's self-tuning mechanism may add one later). --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
