[Info-ingres] Row locking

[Roy Hann]

Mark wrote:

> On Fri, 27 Nov 2015 15:48:26 +0000 (UTC), Roy Hann
> <specially at processed.almost.meat> wrote:
>
>>Mark wrote:
>>
>>> I have an esqlc program which attempts to use row-level locking :
>>
>>[snip]
>>
>>> During the sleep I attempt to update a different record from another
>>> session but it hangs as if the whole table is locked.  How can I get
>>> row-level locking to work?
>>
>>Is the other session also using row-level locking? 
>
> Yes.
>
>>If it is using default (page-level) locking it can't acquire the
>>exclusive page lock it requires and will wait, as you are seeing.
>
> Good suggestion -- but that isn't it.

I ran your code in 10.10.0 (a64.win/125)GPL and it works as expected; no
blocking.

With ING_SET=set lock_trace, from rowtest.exe (creates table,
inserts rows, locks a row):

    LOCK:   MVCC  PHYS                     Mode: IS  Timeout: 0
            Key: (sandbox,row_lock_test_table)
    LOCK:   TABLE PHYS                     Mode: X   Timeout: 0
            Key: (sandbox,row_lock_test_table)
-------------------------------------------------------------------
    LOCK:   MVCC  PHYS                     Mode: IS  Timeout: 0
            Key: (sandbox,row_lock_test_table)
    LOCK:   TABLE PHYS                     Mode: X   Timeout: 0
            Key: (sandbox,row_lock_test_table)
-------------------------------------------------------------------
    LOCK:   MVCC  PHYS                     Mode: IS  Timeout: 0
            Key: (sandbox,row_lock_test_table)
    LOCK:   TABLE PHYS                     Mode: X   Timeout: 0
            Key: (sandbox,row_lock_test_table)
-------------------------------------------------------------------
    LOCK:   MVCC  PHYS                     Mode: IS  Timeout: 0
            Key: (sandbox,row_lock_test_table)
    LOCK:   TABLE PHYS                     Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table)
    LOCK:   PAGE  PHYS,LOCL,NOIN,QUTM      Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0)
    LOCK:   ROW   NOWT,STAT,LOCL,QUTM      Mode: U   Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0,2)
    LOCK:   PAGE  LOCL,QUTM                Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0)
    LOCK:   ROW   STAT,LOCL,QUTM           Mode: U   Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0,2)
    LOCK:   ROW   STAT,LOCL,QUTM           Mode: X   Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0,2)
    LOCK:   PAGE  LOCL,QUTM                Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0)
-------------------------------------------------------------------
press enter to continue...

And from updater.exe (updates a different row):

C:\Users\Roy\Desktop\Junk>updater
Updating...    
    LOCK:   MVCC  PHYS                     Mode: IS  Timeout: 0
            Key: (sandbox,row_lock_test_table)
    LOCK:   TABLE PHYS                     Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table)
    LOCK:   PAGE  PHYS,LOCL,NOIN,QUTM      Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0)
    LOCK:   ROW   NOWT,STAT,LOCL,QUTM      Mode: U   Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0,0)
    LOCK:   PAGE  LOCL,QUTM                Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0)
    LOCK:   ROW   STAT,LOCL,QUTM           Mode: U   Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0,0)
    LOCK:   ROW   STAT,LOCL,QUTM           Mode: X   Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0,0)
    LOCK:   PAGE  LOCL,QUTM                Mode: IX  Timeout: 0
            Key: (sandbox,row_lock_test_table,0.0)
-------------------------------------------------------------------
...and now done!

What version are you running? Do you have ING_SET (in your local
environment or the Ingres symbol table) set to anything confounding?
What is the DBMS system_lock_level set to in CBF?

Roy