SEO

1> select @@version
2> go

--------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/12.5.0.3/EBF 10972 ESD#1/P/Sun_svr4/OS
5.8/rel12503/1919/32-bit/FBO/Tue Mar 25 02:54:09 2003



Can anyone confirm that the problem I am seeing is a bug or not ?
The problem is, when one session did an update to a lock datarows table
and the transaction has not committed or rolled back ... another session
trying to select from that table is blocked, even though I am sure that
the transaction isolation level 1 ( which is the default anyway in ASE )
.... and sp_configure "read committed with lock" shows that it is off.

Here is the case:

SESSION A =======================================

1> create table test_lock_table ( id numeric not null, value varchar not
null ) lock datarows
2> go
1> alter table test_lock_table add constraint test_lock_table_pk primary
key ( id )
2> go


1> insert test_lock_table( id, value ) values ( 1, 'Row 1' )
insert test_lock_table( id, value ) values ( 2, 'Row 2' )
insert test_lock_table( id, value ) values ( 3, 'Row 3' )
insert test_lock_table( id, value ) values ( 4, 'Row 4' )
2> 3> 4>
5> go
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)


2> begin tran
3> go
1> update test_lock_table
2> set value = 'Locking this row ...'
3> where id = 3
4> go
(1 row affected)


SESSION B =======================================
Password:
1> use sgtest
2> go
1> sp_configure "read committed with lock"
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
----------- -------------------- ----------
read committed with lock 0 0 0
0 switch dynamic

(1 row affected)
(return status = 0)
1> set transaction isolation level 1
2> go
1> select * from test_lock_table
2> go


.... It hangs at the point.


SESSION C =========================================

From the output of sp_lock below ... it is clear that SESSION B is
trying to acquire a shared row lock even though it should not, because
"read committed with lock" is 0.


1> sp_lock
2> go
The class column will display the cursor name for locks associated with
a cursor for the current user and the cursor id for other users.
fid spid loid locktype table_id
page row dbname class context

------ ------ ----------- ---------------------------- -----------
----------- ------ --------------- ------------------------------ -------
---------------------
0 50 100 Sh_intent 650690585
0 0 sgtest Non Cursor Lock

0 50 100 Sh_row-request 650690585
1233 2 sgtest Non Cursor Lock

0 63 126 Ex_intent 650690585
0 0 sgtest Non Cursor Lock

0 63 126 Ex_row-blk 650690585
1233 2 sgtest Non Cursor Lock

0 168 336 Sh_intent 768002736
0 0 master Non Cursor Lock


(5 rows affected)
(return status = 0)


Any ideas ?

Never mind .. it is described under "Pseudo Column-Level Locking"


John Salvo wrote:
> 1> select @@version
> 2> go
>
> --------------------------------------------------------------------------------------------------------------------------------------------
>
> -------------------------------------------------------------------------------------------------------------------
>
> Adaptive Server Enterprise/12.5.0.3/EBF 10972 ESD#1/P/Sun_svr4/OS
> 5.8/rel12503/1919/32-bit/FBO/Tue Mar 25 02:54:09 2003
>
>
>
> Can anyone confirm that the problem I am seeing is a bug or not ?
> The problem is, when one session did an update to a lock datarows table
> and the transaction has not committed or rolled back ... another session
> trying to select from that table is blocked, even though I am sure that
> the transaction isolation level 1 ( which is the default anyway in ASE )
> ... and sp_configure "read committed with lock" shows that it is off.
>
> Here is the case:
>
> SESSION A =======================================
>
> 1> create table test_lock_table ( id numeric not null, value varchar not
> null ) lock datarows
> 2> go
> 1> alter table test_lock_table add constraint test_lock_table_pk primary
> key ( id )
> 2> go
>
>
> 1> insert test_lock_table( id, value ) values ( 1, 'Row 1' )
> insert test_lock_table( id, value ) values ( 2, 'Row 2' )
> insert test_lock_table( id, value ) values ( 3, 'Row 3' )
> insert test_lock_table( id, value ) values ( 4, 'Row 4' )
> 2> 3> 4>
> 5> go
> (1 row affected)
> (1 row affected)
> (1 row affected)
> (1 row affected)
>
>
> 2> begin tran
> 3> go
> 1> update test_lock_table
> 2> set value = 'Locking this row ...'
> 3> where id = 3
> 4> go
> (1 row affected)
>
>
> SESSION B =======================================
> Password:
> 1> use sgtest
> 2> go
> 1> sp_configure "read committed with lock"
> 2> go
> Parameter Name Default Memory Used Config Value Run
> Value Unit Type
> ------------------------------ ----------- ----------- ------------
> ----------- -------------------- ----------
> read committed with lock 0 0 0
> 0 switch dynamic
>
> (1 row affected)
> (return status = 0)
> 1> set transaction isolation level 1
> 2> go
> 1> select * from test_lock_table
> 2> go
>
>
> ... It hangs at the point.
>
>
> SESSION C =========================================
>
> From the output of sp_lock below ... it is clear that SESSION B is
> trying to acquire a shared row lock even though it should not, because
> "read committed with lock" is 0.
>
>
> 1> sp_lock
> 2> go
> The class column will display the cursor name for locks associated with
> a cursor for the current user and the cursor id for other users.
> fid spid loid locktype table_id
> page row dbname class context
>
> ------ ------ ----------- ---------------------------- -----------
> ----------- ------ --------------- ------------------------------ -------
> ---------------------
> 0 50 100 Sh_intent 650690585
> 0 0 sgtest Non Cursor Lock
>
> 0 50 100 Sh_row-request 650690585
> 1233 2 sgtest Non Cursor Lock
>
> 0 63 126 Ex_intent 650690585
> 0 0 sgtest Non Cursor Lock
>
> 0 63 126 Ex_row-blk 650690585
> 1233 2 sgtest Non Cursor Lock
>
> 0 168 336 Sh_intent 768002736
> 0 0 master Non Cursor Lock
>
>
> (5 rows affected)
> (return status = 0)
>
>
> Any ideas ?
>