Rowlock appears to be locking entire table.

Without going into a lot of history, I am using embedded SQL in a Micro-Focus

COBOL application.

I have a need to be able to select a specific row from my table and lock that row until I release it.

I have successfully used a declare cursor with a rowlock hint that does lock that row, unfortunately

it also locks the entire table.

I know that SQL Server is best utilized by allowing it it to manage locks, but this is a unique situation

and all I want is a clear answer as to how this should work when I have to do it.

here is a sample of what I have tried. I'm hoping someone has successfully done something

like this (maybe not even in cobol).

EXEC SQL DECLARE TECHLK CURSOR FOR
select
TECH_ID
from TECH_REC
with (rowlock,nowait)
where (TECH_ID = :SQL-TECH-ID)
END-EXEC.

EXEC SQL
OPEN TECHLK
END-EXEC.

EXEC SQL
FETCH TECHLK INTO
:SQL-TECH-ID
END-EXEC.

any help would be appreciated.. thx, Ray Dodson

1. How did you identify that entire table is locked?

2. What queries does your application actually sent to the server? You can figure that out using Profile tool.

3. SQL Server maintain record lock granularity untill it has enough resources for that. Otherwise lock granularity escalated to the table level.

|||

This reply asked more questions than it answered.

1. I used the management item in seql server enterprise manager to see the

locking status.

Actually, I found after this post was sent that my keys were not set up correctly (imported

from another database). that appears to be why I was locking the entire table. I have rectified that

and things seem to be working correctly now.

thanks for you time... Ray Dodson

Row deletion

Hi!
I have a table with the history of blocks for few months. It is
automaticaly filled as new blocks appear. Recently I found that
someone is deleting data from two columns. /* he updates two columns
to NULL value */
It is done by sime kind of sheduled job or trigger, because the data
is deleted right away. The guy who did this is most probably from
development and has sa rights on many servers.
Can I get some step by step hints how to track this?
My best wish is to figure out where this came from...
Perhaps he added a spare step for a job, or created a separate job on
another server, etc.
Addin an 'instead of update trigger' is not an option, cause I, myself
do some updates on this table via job. Another reason is that it is
easy to disable all triggers before the update.
Thanks.Start a SQL Profiler trace and leave it running overnight. In the morning
you should have captured the offending statement and see where it came from.
--
HTH
Ryan Waight, MCDBA, MCSE
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0309290649.1a94a0cc@.posting.google.com...
> Hi!
> I have a table with the history of blocks for few months. It is
> automaticaly filled as new blocks appear. Recently I found that
> someone is deleting data from two columns. /* he updates two columns
> to NULL value */
> It is done by sime kind of sheduled job or trigger, because the data
> is deleted right away. The guy who did this is most probably from
> development and has sa rights on many servers.
> Can I get some step by step hints how to track this?
> My best wish is to figure out where this came from...
> Perhaps he added a spare step for a job, or created a separate job on
> another server, etc.
> Addin an 'instead of update trigger' is not an option, cause I, myself
> do some updates on this table via job. Another reason is that it is
> easy to disable all triggers before the update.
> Thanks.|||In this case SQL Server profiler may help you to some good
level. Through which you can start a new trace.
Add an event for "stmtcompleted" under TSQL event class.
This will capture SQL statements that are getting
executed.
It will give you details about
the "applicationname"/"ntusername"/"loginname" that is
executing the statement.
- Vishal
>--Original Message--
>Hi!
>I have a table with the history of blocks for few months.
It is
>automaticaly filled as new blocks appear. Recently I
found that
>someone is deleting data from two columns. /* he updates
two columns
>to NULL value */
>It is done by sime kind of sheduled job or trigger,
because the data
>is deleted right away. The guy who did this is most
probably from
>development and has sa rights on many servers.
>Can I get some step by step hints how to track this?
>My best wish is to figure out where this came from...
>Perhaps he added a spare step for a job, or created a
separate job on
>another server, etc.
>Addin an 'instead of update trigger' is not an option,
cause I, myself
>do some updates on this table via job. Another reason is
that it is
>easy to disable all triggers before the update.
>Thanks.
>.
>|||If it were me, I would search every one of my stored procs and triggers for
an update statement that updates the table in question. For each stored
proc found, I would determine what other objects (or jobs) call this stored
proc. This should make it somewhat easy to determine how this is happening.
BTW, Largo SQL Tools makes this quite easy to do.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0309290649.1a94a0cc@.posting.google.com...
> Hi!
> I have a table with the history of blocks for few months. It is
> automaticaly filled as new blocks appear. Recently I found that
> someone is deleting data from two columns. /* he updates two columns
> to NULL value */
> It is done by sime kind of sheduled job or trigger, because the data
> is deleted right away. The guy who did this is most probably from
> development and has sa rights on many servers.
> Can I get some step by step hints how to track this?
> My best wish is to figure out where this came from...
> Perhaps he added a spare step for a job, or created a separate job on
> another server, etc.
> Addin an 'instead of update trigger' is not an option, cause I, myself
> do some updates on this table via job. Another reason is that it is
> easy to disable all triggers before the update.
> Thanks.