How to lock only one row

Hello everyone,
I need to:
1. Get data from a table row
2. Lock a row for delete
3. Delete it
The problem is that the code that I use locks the whole table, not just one
row. Does anyone know how to limit the lock to one row for one table?
Thank you,
Helen
P.S.That is what I do:
EXEC SQL
DECLARE ACURS CURSOR FOR
SELECT
"MDC7_PROG_ID",
"MDC7_REV_DATE"
FROM
"MDC7"
WHERE
"MDC7_PROG_ID" = :FMDC7-PROG-ID
END-EXEC
EXEC SQL
OPEN ACURS
END-EXEC
IF SQLCODE <> 0
GO TO END-OF-PROGRAM.
EXEC SQL
FETCH ACURS
INTO
:FMDC7-PROG-ID
, :FMDC7-REV-DATE
END-EXEC
EXEC SQL
DELETE MDC7
WHERE CURRENT OF ACURS
END-EXEC.
IF SQLCODE <> 0
EXEC SQL
CLOSE ACURS
END-EXEC.Helen Stein wrote:
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not
> just one row. Does anyone know how to limit the lock to one row for
> one table?
>
Do you need to be using cursors for this? If not, you can just issue the
delete directly. Unless there are no indexes to help SQL Server locate
the row, you should not see any table locking for the operation.
For example:
Declare @.ID INT
Declare @.OtherKey INT
Set @.OtherKey = 1000
Select @.ID = MyID
From TableA
Where OtherKey = @.OtherKey
If @.ID IS NOT NULL
Delete From TableB Where ID = @.ID
David Gugick
Imceda Software
www.imceda.com|||Why do you need to use a cursor to do a DELETE like this? You are deleting
all rows that are in the current cursor. Wouldn't it be much easier and
faster to write
DELETE MDC7
WHERE MDC7_PROG_ID = <what ever value FMDC7-PROG-ID represents>
How many rows of how many is this operation deleting?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:e4aLcRQFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not just
> one
> row. Does anyone know how to limit the lock to one row for one table?
> Thank you,
> Helen
> P.S.That is what I do:
>
> EXEC SQL
> DECLARE ACURS CURSOR FOR
> SELECT
> "MDC7_PROG_ID",
> "MDC7_REV_DATE"
> FROM
> "MDC7"
> WHERE
> "MDC7_PROG_ID" = :FMDC7-PROG-ID
> END-EXEC
>
> EXEC SQL
> OPEN ACURS
> END-EXEC
> IF SQLCODE <> 0
> GO TO END-OF-PROGRAM.
>
> EXEC SQL
> FETCH ACURS
> INTO
> :FMDC7-PROG-ID
> , :FMDC7-REV-DATE
> END-EXEC
>
> EXEC SQL
> DELETE MDC7
> WHERE CURRENT OF ACURS
> END-EXEC.
>
> IF SQLCODE <> 0
> EXEC SQL
> CLOSE ACURS
> END-EXEC.
>|||The problem is your use of a cursor. It looks like you want something
like this (in TSQL):
SET @.mdc7_rev_date =
(SELECT mdc7_rev_date
FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id)
DELETE FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id
AND mdc7_rev_date = @.mdc7_rev_date
David Portas
SQL Server MVP
--|||The reason why I am using a cursor is because I need to make sure that in
between my select and delete the other user cannot access the row I just
read. Is there any other way of doing this?
Thanks for everybodys reponses.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Helen Stein wrote:
> Do you need to be using cursors for this? If not, you can just issue the
> delete directly. Unless there are no indexes to help SQL Server locate
> the row, you should not see any table locking for the operation.
> For example:
> Declare @.ID INT
> Declare @.OtherKey INT
> Set @.OtherKey = 1000
> Select @.ID = MyID
> From TableA
> Where OtherKey = @.OtherKey
> If @.ID IS NOT NULL
> Delete From TableB Where ID = @.ID
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Isn't handled through transaction isolation?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:Oub2T3QFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> The reason why I am using a cursor is because I need to make sure that in
> between my select and delete the other user cannot access the row I just
> read. Is there any other way of doing this?
>
> Thanks for everybody's reponses.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
>