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
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.
