Re: Selecting Locked rows
Timothy Jones wrote:
>
> Engine 7.14UC1
>
> When you run a select on a particular table, where can you look to see
> if those rows that are being returned, have a lock on them?
Tim,
if you were able to read them, why do you care a rat's tail whether
there is a lock on it? I will assume you are using dirty read in order
to make sure you get the row regardless of other users' activity.
If you like, you might use the primary key in a "for update" cursor and
try to re-fetch the current row and impose an update lock. If there is
already an update or exclusive lock on the row, your "for update" fetch
will fail. However, this will not show any locks imposed on the row by
a user running in "cursor stability" or (shoot'em now) "repeatable read"
isolation levels.
Howzabout this: When you read the row, get the rowid of the row as well.
The use the rowid as a parameter into a query on sysmaster:
select count(*) from syslocks where rowidlk = ?
Of course, this will fall apart if you use fragmentation on your tables,
because "rowid" for fragmented tables refers to an integer unique column
created with the table. In the syslocks pseudo-table, a row-lock is
uniquely identified by the tblspace and rowid within that tblspace.
(This opens a new can of worms. I will soon post a question about
tblspace and rowid in a select statement.)
Bottom line so far: For an unfragmented table, the above will identify
rows locked during the very short interval after the initial read of the
row.
--
-- Jake (With canned apologies to any worms I may have offended)
. .
_..-'( )`-.._
./'. '||\\\\. }\\_/{ .//||` .`\\.
./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\.
./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\.
./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\.
/'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\
'.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.`
'.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.`
|/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\|
V V V }' `\\ /' `{ V V V
\\ \\ \\ V / / /
+-----------------------------------------------------------+
| Impeccable Logic: A thought process which successfully |
| resists chicken bites |
+-----------------------------------------------------------+