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 ?