OdbcConnection : concurrent transaction, IsolationLevel

Hello ,

I need help to How I Can serialize Query with .NetFramework
and Odbc SQLServer Connection ...

I want that :
1. Client A read "name,lastname" from query
2. Client B try to read "name,lastname" but .Net must block the query
3. Client B is waiting that client A have release the block
4. Client A update "name,lastname"
5. Client A close the transaction
6. Client B read "name,lastname" and exit from Block "state"
7. Client B is happy :-))

How I can do it ?!
OdbcTransaction can allow me to do it ?!

thank all

Hakù

David
Thu May 06 17:15:41 CDT 2004


"Hakùna kù" <hk@hk.com> wrote in message
news:OUs91i6MEHA.2704@TK2MSFTNGP10.phx.gbl...
> Hello ,
>
> I need help to How I Can serialize Query with .NetFramework
> and Odbc SQLServer Connection ...
>
> I want that :
> 1. Client A read "name,lastname" from query
> 2. Client B try to read "name,lastname" but .Net must block the query
> 3. Client B is waiting that client A have release the block
> 4. Client A update "name,lastname"
> 5. Client A close the transaction
> 6. Client B read "name,lastname" and exit from Block "state"
> 7. Client B is happy :-))
>
> How I can do it ?!
> OdbcTransaction can allow me to do it ?!
>

Yes. Each client should enlist all Commands in a transaction.

Then if client A issues
SELECT name,lastname from t with(updlock) where id = ?

This will select one row and lock it for the duration of client A's
transaction.

David

David

Hakùna
Fri May 07 02:26:29 CDT 2004

> Then if client A issues
> SELECT name,lastname from t with(updlock) where id = ?
>
> This will select one row and lock it for the duration of client A's
> transaction.

Hello ,

But :

myTransaction.BeginTransaction(IsolationLevel.ReadCommited);
myCommand.transaction = myTransaction;

What is ?
My code not lock the row/s affected of Query ?

And what is "updlock" ?
How I find documentation on this method ?

Hukù

David
Fri May 07 08:30:41 CDT 2004


"Hakùna kù" <hk@hk.com> wrote in message
news:OvgMfSANEHA.2468@TK2MSFTNGP11.phx.gbl...
> > Then if client A issues
> > SELECT name,lastname from t with(updlock) where id = ?
> >
> > This will select one row and lock it for the duration of client A's
> > transaction.
>
> Hello ,
>
> But :
>
> myTransaction.BeginTransaction(IsolationLevel.ReadCommited);
> myCommand.transaction = myTransaction;
>
> What is ?
> My code not lock the row/s affected of Query ?
>
> And what is "updlock" ?

Use update locks instead of shared locks while reading a table, and hold
locks until the end of the statement or transaction. UPDLOCK has the
advantage of allowing you to read data (without blocking other readers) and
update it later with the assurance that the data has not changed since you
last read it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_1hf7.asp

> How I find documentation on this method ?
>

SQL Books Online, or MSDN


David