How many user current "connect" to a row in table.

Hi all...
Iam a newbie and i have one question want to ask experts ... .
I am current working on one App which have some user connect to database at the same time.
And i want to know: how many user connect or use (read , update) to one row in a table?
Could it be possible to know that? If you have one solution to solve this problem , please let me know :) ...
Could it be done by Software , T-SQL or anything ...., iam happy to know.
Thanks all.

Hello,

There's actually no straightforwarffd answer as this will depend upon the transaction isolation level which will in turn determine how many transactions may read a row as well as what state the data read will be in (dirty, comitted etc). Only one transaction can ever modify a row.

Have a look at http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx and http://msdn2.microsoft.com/en-US/library/ms173763.aspx as well as BOL for information.

Cheers,

Rob

|||Iam using SQL server 2000 and don't have intended to update to sql server 2005, so i can't use RLV.
I ask this question because i want to notify my clients when data was changed by users.
Is there any idea for me to solve this problem? if have , please show me more details
Thanks .:)|||Couldn't you try to use a trigger to make such a mecanism ?|||I think when a user changed data on the table and trigger on that table was fired then only that user was recieved alert from Server. Do you think so ?|||A trigger is fired on a statement level. When the trigger is fire you could write a message / row in a event table which is polled by the application. In another table you could save the information that the application of User A has received the notification but the application of user B did not. Althouh the trigger is fired once you could spread the *news* anout the changed data.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your advise.
But i think traffic network is expensive resource so if my App keep polled the table which store info about what table has changed , it'll impact my performence which i want to avoid.
do you have anothet idea?
Thanks again.|||

Can you state the underlying purpose to what you are doing? It is not a natural thing to be interested in what row a user might be "connected" to. Now if you are talking about locking it for modification, that makes it a bit more clear, and you could use some form of lock to make that happen.

The thing is, SQL works in a very set oriented architecture, so you might touch a million rows when you think you end up only looking at one (it might require a scan of all values to find just one). SQL Server does nice things with locking, and there are techniques like optimistic locking that you can use to enhance concurrency, so if you could give a bit more information we might could suggest a better way.

|||

Louis Davidson wrote:

Can you state the underlying purpose to what you are doing? It is not a natural thing to be interested in what row a user might be "connected" to. Now if you are talking about locking it for modification, that makes it a bit more clear, and you could use some form of lock to make that happen.

The thing is, SQL works in a very set oriented architecture, so you might touch a million rows when you think you end up only looking at one (it might require a scan of all values to find just one). SQL Server does nice things with locking, and there are techniques like optimistic locking that you can use to enhance concurrency, so if you could give a bit more information we might could suggest a better way.

Thanks for your post :).

My purpose at

begin is Sql server or My App will notify my client when data was

changed for any client who current connect to that row.
For example:
-user A connect and load row R1 into dataset(vb.net).
-user B also connect to that table and load row R2
- R1 and R2 has some column which has the same data
==>

when user change data on some column and finished it,a litle later,

because user B did not know data was changed and continue changed that

data...so if i sum on total column(for example)then may be the value

was double and that is what i don't expect.

Sorry about my poor English, but if you understand what i mean , please give me your advise
Thanks.