Flemming Frandsen wrote:
> "Zabach, Elke" wrote:
>
> > > I'd like to insert into a table, without *ever* setting a
> single lock.
> > It is NOT possible to avoid locking of a dml
> > (insert/update/delete)-call.
>
> That's ok as long as I can avoid checking for the locks.
>
>
> > Let those other processes use isolation level 0 or this
> special select
> > (see reference manual --> ...--> lock_option
> >
> >
> http://www.sapdb.org/htmhelp/e2/55683ab81fd846e10000000a11402f
> /frameset.htm
>
> Hmm, the page doesn't work on Konqueror, boo, hiss.
>
> Nevermind I have pdf reader.
>
> Page 197:
> <lock_option> ::= WITH LOCK [(NOWAIT)] [EXCLUSIVE] [ISOLATION LEVEL
> <unsigned_integer>] WITH LOCK [(NOWAIT)] OPTIMISTIC [ISOLATION LEVEL
> <unsigned_integer>]
>
>
> So if I tack:
> WITH LOCK ISOLATION LEVEL 0
> or
> WITH LOCK OPTIMISTIC ISOLATION LEVEL 0
> Onto my selects then they will not check for or set locks?
>
> * Am I right in assuming that level 0 means that the select can never
> cause a deadlock?
YES
> * Am I right in assuming that the OPTIMISTIC keyword doesn't mean
> anything when level 0 is used?
NO, OPTIMISTIC lock is done, which will not collide (see answer to
first question)
but will check if an update is done intermediately by the time the
dml-command
for this row will arrive in the kernel-locking-mechanism
> * What locks are set when I execute:
> select sum(somecol) from sometable where mumble=42
> in isolation level 1, 2 and 3?
3: a share lock for sometable hold by the end of the transaction
2: a temporary share lock for sometable by the time all rows belonging
to the resulting cursor are found resp. the cursor is closed
(in case of sum, all rows have to be searched during select;
resp. in case where EXPLAIN says ' result is NOT copied' searching
the
resultrows is done during fetch --> temp share lock until CLOSE of
cursor)
+ !
share row lock for the qualified rows resp. the qualified and
FETCHED rows
(NOT copied result) by the end of the session
1: none, it will just checked during selecting (and waited for) if
another task
has an exclusive lock of one of the needed rows.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
