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