row id

[unknown]

i want to get the row id of a row in a table with datarows
locking ? how do i get it?

I want to verify following:
Insert row R1 in t1 (datarows lock scheme)
check rowid for R1
delete row R1 from t1
commit
insert the same row R1 in t1
check rowid for R1

is it same? Would sybase place the row in the same phsyical
location?

[Bret Halford]

Post by unknown
i want to get the row id of a row in a table with datarows
locking ? how do i get it?
Insert row R1 in t1 (datarows lock scheme)
check rowid for R1
delete row R1 from t1
commit
insert the same row R1 in t1
check rowid for R1
is it same? Would sybase place the row in the same phsyical
location?

The location could be the same or different. It is more likely to
be the same location if the table has a placement (clustered) index,
but even then could well be different.

The row id for a DOL table row is a combination of the number
of the page it is on and the number of the row on the page.

You can determine what it is by using the syslocks table in master
to see which row gets an exclusive lock when you update it.

create table t1 (x int) lock datarows
go
insert t1 values (1)
go
begin tran
update t1 set x = 3 where x = 1
select dbid, object_name(id) as "table", page, row from master..syslocks
where spid = @@spid and type = 8
rollback tran

delete t1 where x = 1
go

insert t1 values (1)
go
begin tran
update t1 set x = 3 where x = 1
select dbid, object_name(id) as "table", page, row
from master..syslocks
where spid = @@spid and type = 8
rollback tran

[unknown]

so it doesnt maintain the same rowid.
I tested for 10000 rows. deleted 5999 row.
before delete
dbid table page row
------ ------------------------------ ----------- ------
2 t1 1310 22

after re-insert of 5999 row

dbid table page row
------ ------------------------------ ----------- ------
2 t1 1334 40

Post by Bret Halford

Post by unknown
i want to get the row id of a row in a table with
datarows locking ? how do i get it?
Insert row R1 in t1 (datarows lock scheme)
check rowid for R1
delete row R1 from t1
commit
insert the same row R1 in t1
check rowid for R1
is it same? Would sybase place the row in the same
phsyical location?

The location could be the same or different. It is more
likely to be the same location if the table has a
placement (clustered) index, but even then could well be
different.
The row id for a DOL table row is a combination of the
number of the page it is on and the number of the row on
the page.
You can determine what it is by using the syslocks table
in master to see which row gets an exclusive lock when you
update it.
create table t1 (x int) lock datarows
go
insert t1 values (1)
go
begin tran
update t1 set x = 3 where x = 1
select dbid, object_name(id) as "table", page, row from
rollback tran
delete t1 where x = 1
go
insert t1 values (1)
go
begin tran
update t1 set x = 3 where x = 1
select dbid, object_name(id) as "table", page, row
from master..syslocks
rollback tran

[Species8472]

Post by unknown
i want to get the row id of a row in a table with datarows
locking ? how do i get it?
Insert row R1 in t1 (datarows lock scheme)
check rowid for R1
delete row R1 from t1
commit
insert the same row R1 in t1
check rowid for R1
is it same? Would sybase place the row in the same phsyical
location?

For performance reasons ASE will perform a logical delete on a row
(which can be cleared with the reorg command), and a re-insert of the
same data will go to a different location.