handling concurrency while OPENING a record with using transactions

  • Thread starter Thread starter Ive
  • Start date Start date
I

Ive

Hi,

I'm developping a couple of applications in .NET (VB more specifically) on
top of SQL Server 2000.

I use transactions in ADO.NET to make sure that when someone opens a record
which is already opened by another user, the second user can't make changes
to the record. I use Stored procs everywhere to open, create or update
records. The isolation level of the transaction is "repeatable read".

Everything is working fine, but when a second copy of a record is opened I
can't seem to be able to find out whether this record is already locked for
the transaction while opening. I tried @@TRANCOUNT in my Stored proc but
this doesn't seem to hold a correct figure.

I would like to show a messagebox or something similar in which there is
"Read Only" so the user knows his record is read-only.

What am I doing wrong? Should I use another isolation level or another way
to achieve this?

Any help is greatly appreciated!

Ive
 
I should not used transactions to solve this problem.
Instead I should have created a new column in the database
the I set to 1 if the current row is read only and 0 if
its not read only. Transactions will affect the
performance and you should try to avoid using transactions
when you read data.

/Fredrik Normén NSQUARED2
 
Dear Fredrik,

Thanks for the post.

I will combine transactions with what you said. I already thought about the
solution in the beginning but I was hoping I could do everything with
transactions. I like the idea of transactions though.

The performance is very good, is it really affecting performance that much?

Ive

I should not used transactions to solve this problem.
Instead I should have created a new column in the database
the I set to 1 if the current row is read only and 0 if
its not read only. Transactions will affect the
performance and you should try to avoid using transactions
when you read data.

/Fredrik Normén NSQUARED2
 
@@TRANSACTION returns the number of active transactions for the current
connection. The ADO.NET use connection pool, so the @@TRANSACTION returns
different values if the users use different connections (determined by
ADO.NET).

It is difficult to let the user to know the status of the traction until he
tries to update the record and captures any exception.


Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Reply-To: "Ive" <[email protected]>
| From: "Ive" <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: Re: handling concurrency while OPENING a record with using
transactions
| Date: Sat, 27 Sep 2003 09:50:31 +0200
| Lines: 65
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: 119.105-136-217.adsl.skynet.be 217.136.105.119
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62262
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Dear Fredrik,
|
| Thanks for the post.
|
| I will combine transactions with what you said. I already thought about
the
| solution in the beginning but I was hoping I could do everything with
| transactions. I like the idea of transactions though.
|
| The performance is very good, is it really affecting performance that
much?
|
| Ive
|
| | I should not used transactions to solve this problem.
| Instead I should have created a new column in the database
| the I set to 1 if the current row is read only and 0 if
| its not read only. Transactions will affect the
| performance and you should try to avoid using transactions
| when you read data.
|
| /Fredrik Normén NSQUARED2
|
|
| >-----Original Message-----
| >Hi,
| >
| >I'm developping a couple of applications in .NET (VB more
| specifically) on
| >top of SQL Server 2000.
| >
| >I use transactions in ADO.NET to make sure that when
| someone opens a record
| >which is already opened by another user, the second user
| can't make changes
| >to the record. I use Stored procs everywhere to open,
| create or update
| >records. The isolation level of the transaction
| is "repeatable read".
| >
| >Everything is working fine, but when a second copy of a
| record is opened I
| >can't seem to be able to find out whether this record is
| already locked for
| >the transaction while opening. I tried @@TRANCOUNT in my
| Stored proc but
| >this doesn't seem to hold a correct figure.
| >
| >I would like to show a messagebox or something similar in
| which there is
| >"Read Only" so the user knows his record is read-only.
| >
| >What am I doing wrong? Should I use another isolation
| level or another way
| >to achieve this?
| >
| >Any help is greatly appreciated!
| >
| >Ive
| >
| >
| >.
| >
|
|
|
 
Thanks All,

I'm still using transactions.

But I have included a bit-field which now gives me an indication whether the
record is opened or not.

Transaction level the least-restrictive.

Ive
 
Back
Top