Does anybody has any suggestions? This issue is really killing us. We have multiple processes that need to access this table, select the first available record, use the certificate number in that table and then mark it as used. Without the ability to do row-level locking, multiple processes are overwriting each other's transactions and without limiting the result of the query to 1 record is resulting in extreemley poor performance. I really need to figure out if this is a nhibernate bug or I am not using it correctly.
Please help...... Waqar On Aug 31, 3:04 pm, Waqar Sadiq <[email protected]> wrote: > I have a need to retrieve the first row from a table that matches a > certain criteria, with row level lock. If I retrieve the first row > that matches the criteria, without the row level lock, it works fine. > Also, it works fine if apply the row level lock, withouth limiting the > number fo records. But when I use the two in conjunction, nhibernate > generates an invalid query for Oracle. > > My code looks somewhat like this. > > -------------------- My Code --------------------------- > ICriteria crit = > CurrentSession.CreateCriteria(typeof(RewardBarCodes)); > crit.SetLockMode(lockMode); > crit.Add(Expression.Eq("TypeCode", "ABCDEF")); > crit.Add(Expression.Eq("Status", 0)); > crit.SetFirstResult(1); > crit.SetMaxResults(batchInfo.BatchSize); > IList < IClientDataObject> list = crit.List<IClientDataObject>(); > return list; > ----------------------------------------------------------------- > > This code results in the following query being generated. > > ----------------------- NHibernate generated query > ---------------------- > select * > from (select row_.*, > rownum rownum_ > from (SELECT this_.A_RowKey as A1_93_0_, > this_.A_IpCode as A2_93_0_, > this_.A_ParentRowKey as A3_93_0_, > this_.A_TypeCode as A4_93_0_, > this_.A_BarCode as A5_93_0_, > this_.A_Status as A6_93_0_, > this_.StatusCode as StatusCode93_0_, > this_.CreateDate as CreateDate93_0_, > this_.UpdateDate as UpdateDate93_0_, > this_.LastDmlId as LastDmlId93_0_ > FROM ats_RewardBarCodes this_ > WHERE this_.A_TypeCode = 'ABCDEF' /* :p0 */ > and this_.A_Status = 0 /* :p1 */) row_ > where rownum <= 2 /* :p2 */) > where rownum_ > 1 /* :p3 */ > for update of this_.A_RowKey > -------------------------------------------------------------------------------------------------- > > This query fails in Oracle because it does not recognize A_RowKey, > which is the primary key, outside of the inner select statement. > > Has anybody come across this situation before and can offer any help. > > Thanks in advance. > > Waqar -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
