I made some experiments and found that what I told you about using
NOLOCK is not actually true. Axapta will only use NOLOCK on those
tables which have CacheLookup set to Found, Found&Empty or EntireTable
(whether inside or outside of tts scope). Some of the "popular" tables
have this setting, some may not. So when Axapta is reading from tables
without appropriate CacheLookup set select will not have NOLOCK clause
so you might have the classic reader-waits-for-writer situation. This
is the case I'm talking about in point 5. It is my understanding now
that selects with NOLOCK clause will never wait for a lock, at least I
was not able to create such a situation myself.
Other then that - when using SQL Server you can expect some additional
writers vs. writers locking issues other that when two transactions
are trying to update the same row. Eg. two simultaneous inserts to the
some table will cause one of the transactions to wait.
Sorry replying so late and good luck
--
lewickiw
--- In [email protected], "Kang Shang Yie"
<[EMAIL PROTECTED]> wrote:
> Hi Lewickiw,
>
> Thanks, your reply has cleared some of the doubts on my mind. For the
> item 5 that you mentioned below, have you experienced any scenario
> like that ? Hope you can share it if you met this problem before ?
>
> Looking forward for your reply, thanks.
>
> Kind Regards,
> Kang
>
>
> --- In [email protected], "lewickiw" <[EMAIL PROTECTED]>
> wrote:
> >
> > Hi
> >
> > First, no explicit table locks are issued by Axapta. Locks are only
> > created implicitly by insert/update/delete/select forupdate
> > statements. At least AFAIK. There's nothing you can do about them
> > other then restructure your code wrt sql statements.
> >
> > Second - it does not matter whether query is executed from static or
> > dynamic method or job or whatever.
> >
> >
> > Here is a quick summary of how database locking works in Axapta :
> >
> > 1. locks are only acqired for the lifetime of a transaction, locks
> are
> > freed when transaction ends, if no tts-block is active each single
> > statement is a transaction, locks are then freed when statement ends
> >
> > 2. normal selects (w/o forupdate) within tts block or not will never
> > acquire locks, neither read nor write lock (note : if SQL Server is
> > used Axapta will use dirty reads to faciliate non-locking reads,
> > Oracle allows no-locking reads in read commited mode using MVRC)
> >
> > 3. select forupdate will lock selected records in exclusive mode
> (read
> > & write), forupdate declaration is IGNORED if not within tts block,
> > exact semantics differ between SQLServer and Oracle : SQL Server
> locks
> > records as they are retrieved, Oracle locks ALL returned rows first,
> > before returning the first row from a query
> >
> > 4. updates will lock updated records in exclusive mode (read &
> write)
> >
> > 5. updates/inserts/deletes statesments will not cause any additional
> > locking on Oracle (other then the updated row being locked in an
> > update statement), they will however acquire IX locks on the
> modified
> > table on SQLServer, this may sometimes cause readers to stall (but
> > sometimes not, this depends, I don't know the exact rules)
> >
> >
> > With SQL Server things can get more complicated because of lock
> > escalation :-(
> >
> >
> > Have fun
> > --
> > lewickiw
> >
> > --- In [email protected], "Kang Shang Yie"
> > <[EMAIL PROTECTED]> wrote:
> > > Hi all,
> > >
> > > Is there anyone can give me a clear picture on Axapta Transaction
> > > Control and Data Locking ? There is few queries in my mind as I
> have
> > > encountered performance issue on my client live site.
> > >
> > > 1. Within the ttsBegin..ttsCommit, will Axapta issue table
> locking on
> > > all querying tables data ? or it varies or depends on our SQL
> > > statements ?
> > >
> > > 2. Anyway to better reform our queries to make sure every single
> SQL
> > > will not do table locking but records or rows locking only ?
> > >
> > > 3. Will static method cause read-locking on the data we selected ?
> > >
> > > Please advise as any of your comments is greatly appreciated !
> > > thanks !
> > >
> > >
> > > Kind Regards,
> > > Kang
SPONSORED LINKS
| Computer part | Programming languages | Microsoft axapta |
| Support exchange |
YAHOO! GROUPS LINKS
- Visit your group "development-axapta" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
