I was wondering if there was a simple script that could be run or a way to h
ave an entry created in a log file whenever a SQL Lock occurs. Specifically
if it could also give the username of the user running the query that creat
ed the lock. Thank you.You could have a profiler trace running. But be aware that the locking
activity in SQL Server can be *very* high! Do a test first so you don't
overload the system with the logging.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.|||Tibor, Thanks for the reply. I tried profiling but noticed the performance
decrease. All I really would like is basically something that alerts me via
either a log file I can check daily or a netsend message that tells me when
the database gets blocked
by a SPID and who it is that is causing the block. I don't actually need to
monitor every lock as I found out with the profiler. Thank you.|||INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/defaul...kb;EN-US;271509
Also have a look at
INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;EN-US;224587
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/defaul...kb;EN-US;243589
INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
http://support.microsoft.com/defaul...b;EN-US;Q224453
As well as these articles themselves, they contain links in them to lots
of other performace troubleshooting type articles. Lots of good stuff !
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.
Showing posts with label locks. Show all posts
Showing posts with label locks. Show all posts
Monday, March 19, 2012
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...
>
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...
>
Subscribe to:
Posts (Atom)