VB.NET and SQL Server 2005 - Data locked when they should be. LOOK

  • Thread starter Thread starter Dominic Belley
  • Start date Start date
D

Dominic Belley

Hi,

I have 2 ways of selecting data from my table "Membre".

1- By permit number (memNoPermis)
2- By lastname, firstname (memNom AND memPrenom)

SAMPLE DATA:

memNoPermis: 1
memNom: Walter
memPrenom: Gregory

memNoPermis: 2
memNom: Wick
memPrenom: Timothee

memNoPermis: 3
memNom: Williams
memPrenom: Simon

He is my weird scenario.

FIRST- I run this query.

SELECT M.memNoPermis, M.memNom, M.memPrenom
FROM Membre M WITH (UPDLOCK, NOWAIT)
WHERE M.memNoPermis = 2

It reads & locks the record ! FINE.
-----

After, if I run this query :
SELECT M.memNoPermis, M.memNom, M.memPrenom
FROM Membre M WITH (UPDLOCK, NOWAIT)
WHERE M.memNom = 'Williams' AND M.memPrenom = 'Simon'

It tells me its lock ... but that is false. Only record id 2 should be
locked. The query above should return unlocked record id 3.

BUT

If I run the following query instead.. which ask for the same record.

SELECT M.memNoPermis, M.memNom, M.memPrenom
FROM Membre M WITH (UPDLOCK, NOWAIT)
WHERE M.memNoPermis = 3

In this case the record can be selected as if it wasn't locked (which is
the case).

Can anyone help me on this?
memNoPermis is my table PrimaryKey field.

Could my problem be related to that?

I made many tests. If I only read records with "memNoPermis" in my WHERE
clause, everything works perfectly.

BUT.

More explanation:

If I read record id 3 with memNom+memPrenom and no row it locked ! GREAT.
READS & LOCKS.

While record id 3 is locked:
If I read record id 2 (which is before 3 in the table) with memNom+memPrenom
.... GREAT. READS & LOCKS.
BUT
Instead if I read record id 4 (which is after 3 in the table) .. it acts as
if I was locked already...which is wrong.

Pretty strange.
When reading with (memNom+memPrenom) in my WHERE clause, If the record I
need is after a locked record in my table, it acts as if it was locked
already.
When reading with (memNoPermis <-- PK field) in my WHERE clause, In all
cases it always works great !!

Thanks for helping me resolving this problem.
I tried putting memNoPermis+memNom+memPrenom in a UniqueKey. But it didn't
help.
 
Dominic,

You can say using SQL via the Net sqlcommands is Adonet.
In my idea are your questions however completely related to the SQL
scripting for Server2005

There are enough here who have an answer for this, so feel free to ask it, I
give you however a better change in a special SQL server programming related
newsgroup.

I hope this helps,

Cor
 
Back
Top