I have to lock the row while insertion or at updation when many users
accessthe particular row.i f the first user access the row after at that tim
e
the row should be locked after his updation the row will unlock since many
users not have to updation at the same time.so ihave lock and release the ro
w
in the stored procedureIf I understood your post correctly, you need some sort of check-out
management. This is not provided natively by SQL Server 2000, and can be
achieved in several ways.
If you specify your needs and provide the DDL and some sample data, maybe we
can help you find a solution.
ML|||hi ML,
I will explain in clearly.
By Using frontend(PHP forms)my clients can update a particular row in
particular table at that same time.while updating i have to update th versio
n
number in the table.so i took the maxvalue of that column at that particular
row and update version mumber.while at that time of updating max count two o
r
more clients gets the same value of that the particular column at that same
time.so it will crasing the number.so i have to lock that paricular while on
e
updating if another tries it displays a message some one is updating,so
please wait a moment and try after his updation is over the row lockis
released and another client able to sccess that row and update.
so how can lock that row and release the lock
it is long query,the query depends many so i can't able to send the query
directly
regards,
balakarthik
"ML" wrote:
> If I understood your post correctly, you need some sort of check-out
> management. This is not provided natively by SQL Server 2000, and can be
> achieved in several ways.
> If you specify your needs and provide the DDL and some sample data, maybe
we
> can help you find a solution.
>
> ML|||balakarthik
You have already asked this in .server and already have been given some
references there. Please do not multipost, it make is very confusing for
those people who are trying to help, who don't know what has already been
said.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
> version
> number in the table.so i took the maxvalue of that column at that
> particular
> row and update version mumber.while at that time of updating max count two
> or
> more clients gets the same value of that the particular column at that
> same
> time.so it will crasing the number.so i have to lock that paricular while
> one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>|||You need to use SELECT MAX...FROM...WITH(UPDLOCK, HOLDLOCK) to get the
maximum value, and the locks must be held until the INSERT/UPDATE completes.
If you issue INSERT...SELECT MAX...FROM ...WITH(UPDLOCK, HOLDLOCK) then the
INSERT statement provides an implicit transaction. If you issue SELECT
@.localVariable = MAX...FROM...WITH(UPDLOCK, HOLDLOCK) INSERT...VALUES, then
you need to enclose both statements within a transaction. WITH(UPDLOCK,
HOLDLOCK) prevents other transactions from issuing an insert or update that
has a value for the column that is between MAX and infinity until the lock
is released. Both UPDLOCK and HOLDLOCK are required because you need an
update range-lock, not an individual update lock, or a shared range-lock.
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
version
> number in the table.so i took the maxvalue of that column at that
particular
> row and update version mumber.while at that time of updating max count two
or
> more clients gets the same value of that the particular column at that
same
> time.so it will crasing the number.so i have to lock that paricular while
one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>
maybe we|||I see.
Let us forget for a minute about rows, columns, tables and databases. Let's
discuss entities.
Let's say your entity is a single document in a drawer. But this is not an
ordinary drawer, since many users can read the same document at any given
moment. However, only one of them should be allowed to change the contents o
f
that document at any given time.
What you need to do is to let other users know that changes to a document
cannot be made if that document is "checked-out".
In a database you need to do something like this:
1) make sure all data access is done through procedures; and
2) make sure procedures cannot make changes to documents that are
checked-out; and
3) make sure the appropriate procedures report check-outs to the database.
One way is to add a column (or two) to the master table (BTW: since I
haven't seen your DDL this is more a guess than a fully valid suggestion):
CheckedOutBy - it holds the username of the person who checked out the
document. When null, the document is not checked out.
(Maybe even CheckedOutSince - it holds the time of the check-out.)
So, in a use case you'd do something like:
when a user gets a document that hasn't been checked-out (CheckOutBy is
null), the reading procedure sets CheckedOutBy to store the name of the
current user; and when a user finishes his changes the procedure then sets
CheckedOutBy back to null;
any user that tries to check out a document that is already checked out by
another user is warned that his changes will not succeed, and the applicatio
n
should prevent any changes to be made.
But, this is just *one* way of doing it.
ML|||Thanks sir i wil try
"ML" wrote:
> I see.
> Let us forget for a minute about rows, columns, tables and databases. Let'
s
> discuss entities.
> Let's say your entity is a single document in a drawer. But this is not an
> ordinary drawer, since many users can read the same document at any given
> moment. However, only one of them should be allowed to change the contents
of
> that document at any given time.
> What you need to do is to let other users know that changes to a document
> cannot be made if that document is "checked-out".
> In a database you need to do something like this:
> 1) make sure all data access is done through procedures; and
> 2) make sure procedures cannot make changes to documents that are
> checked-out; and
> 3) make sure the appropriate procedures report check-outs to the database.
> One way is to add a column (or two) to the master table (BTW: since I
> haven't seen your DDL this is more a guess than a fully valid suggestion):
> CheckedOutBy - it holds the username of the person who checked out the
> document. When null, the document is not checked out.
> (Maybe even CheckedOutSince - it holds the time of the check-out.)
> So, in a use case you'd do something like:
> when a user gets a document that hasn't been checked-out (CheckOutBy is
> null), the reading procedure sets CheckedOutBy to store the name of the
> current user; and when a user finishes his changes the procedure then sets
> CheckedOutBy back to null;
> any user that tries to check out a document that is already checked out by
> another user is warned that his changes will not succeed, and the applicat
ion
> should prevent any changes to be made.
> But, this is just *one* way of doing it.
>
> ML
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Wednesday, March 28, 2012
Row-level Security: Permissions required on base table?
I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.
Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.
Yes, that is expected behavior.
Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.
If you have been GRANTed permission for a table, obviously you have permission.
If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.
But IF you have been explicited DENY(ied), that 'trumps' all.
Think of it this way.
Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).
However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.
So anytime permission is an explicit DENY, there is no route around it.
Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.
|||Thanks for responding.
Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.
I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.
|||I would suggest the following topics from BOL:
· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views
· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||In addition to Raul's suggestions, I offer the following insight.
Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)
SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.
|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.
I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks to both you and Arnie for responding to this inquiry.
The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.
I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.
Again, thanks for your help.
Row-level Security: Permissions required on base table?
I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.
Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.
Yes, that is expected behavior.
Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.
If you have been GRANTed permission for a table, obviously you have permission.
If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.
But IF you have been explicited DENY(ied), that 'trumps' all.
Think of it this way.
Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).
However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.
So anytime permission is an explicit DENY, there is no route around it.
Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.
|||Thanks for responding.
Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.
I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.
|||I would suggest the following topics from BOL:
· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views
· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)
I hope this information helps,
-Raul Garcia
SDE/T
SQL Server Engine
|||In addition to Raul's suggestions, I offer the following insight.
Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)
SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.
|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.
I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks to both you and Arnie for responding to this inquiry.
The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.
I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.
Again, thanks for your help.
sqlRow-level locking & concurrent access
I have a VB6 rich client application that accesses SQL Server 2000
using ADO. The database has a table of customers. The application has
a form that shows customer details, and allows the user to navigate the
to next/previous customer in the table.
When user A is the first to open customer 1's details, I want them to
"take ownership" of that record, such that only user A can make changes
to the record. When user B navigates to customer 1, I want user B to
be able to view the customer's details, but not be able to change them.
However, if user B navigates to customer 2, whose record is on the same
data page as customer 1, I do want user B to be able to take ownership
of customer 2's record, and be able to make changes.
In short, I want row-level locking on the customers table. I was
relying on the client updating a customer record to indicate they have
ownership, but ADO caches changes for (I believe) 5 seconds, so if two
users view the same customer within five secs of each other, the second
user cannot ascertain that the first user has taken ownership.
How do I implement row-level locking? Or is there another way?Davy
UPDATE statement creates an exclusive lock on the row so if the another user
wants to change/update the same row he/she will be locked, however what if
you have ( you probably have ) more details for the customer , so in that
case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
which makes updating
Also take a look at transaction isolation levels:
a.. READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Thanks, Uri. Most grateful.
I still have a problem, though. I have set up two Query Analyzer
sessions, representing User A and User B. The first connection runs
this:
begin tran
update Customers set Tel='01234 567 890' where ID=1
Without committing the transaction, this SQL does apply an exclusive
lock on the customer with ID 1.
This User B SQL works well too:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from Customers where ID=1
User B can read the dirty data.
However, if user B goes to customer with ID 2, this will not work:
update Customers set Name='My Customer Inc.' where ID=2
It seems that the User A transaction is not applying a row-level lock,
but rather a page lock, or even table lock. How can I force the UPDATE
in user A's session to only lock one customer row?
Uri Dimant wrote:
> Davy
> UPDATE statement creates an exclusive lock on the row so if the another user
> wants to change/update the same row he/she will be locked, however what if
> you have ( you probably have ) more details for the customer , so in that
> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
> which makes updating
> Also take a look at transaction isolation levels:
> a.. READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Davy
Do you have an index on ID column?
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||Yes. It's the primary key. Is there an implication?
Uri Dimant wrote:
> Davy
> Do you have an index on ID column?
>
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Hi
Row level locking would be occurring at data level, but if a modification of
an index is involved, you might have blocking on the index page.
Look at the output of sp_lock to see what type of lock is causing the
problem.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||You've described a 'pessimistic concurrency' technique. Here's an
description from the SQL Server 2005 Books Online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
Concurrency control theory has two classifications for the methods of
instituting concurrency control:
Pessimistic concurrency control
A system of locks prevents users from modifying data in a way that affects
other users. After a user performs an action that causes a lock to be
applied, other users cannot perform actions that would conflict with the
lock until the owner releases it. This is called pessimistic control because
it is mainly used in environments where there is high contention for data,
where the cost of protecting data with locks is less than the cost of
rolling back transactions if concurrency conflicts occur.
Optimistic concurrency control
In optimistic concurrency control, users do not lock data when they read it.
When a user updates data, the system checks to see if another user changed
the data after it was read. If another user updated the data, an error is
raised. Typically, the user receiving the error rolls back the transaction
and starts over. This is called optimistic because it is mainly used in
environments where there is low contention for data, and where the cost of
occasionally rolling back a transaction outweighs the costs of locking data
when read.
</Excerpt>
Although you could use SQL Server's row locking to implement pessimistic
concurrency, that would be especially bad for your application because it
would require that you keep the transaction open while waiting for user
action. Consider that the user may go to lunch or take the afternoon off
while viewing customer data. A cardinal rule is that one should never keep
a transaction open while waiting for user action.
I suggest you consider using an optimistic concurrency approach instead.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Dave
I did just testing and it worked for me just fine, perhaps I missunderstood
you.
--Open connection1 (userA)
--Run
create table t (c1 int primary key,c2 char(1))
insert into t values (1,'a')
insert into t values (2,'b')
begin tran
update t set c2='c' where c1=1
--rollback tran
--Open connection2 (userB)
--Run
select * from t where c1 =2
update t set c2='t' where c1=2
--After I ssued ROLLBACK at connection1 i see
c1 c2
-- --
1 a
2 t
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> Yes. It's the primary key. Is there an implication?
>
> Uri Dimant wrote:
>> Davy
>> Do you have an index on ID column?
>>
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
>> > Thanks, Uri. Most grateful.
>> >
>> > I still have a problem, though. I have set up two Query Analyzer
>> > sessions, representing User A and User B. The first connection runs
>> > this:
>> >
>> > begin tran
>> > update Customers set Tel='01234 567 890' where ID=1
>> >
>> > Without committing the transaction, this SQL does apply an exclusive
>> > lock on the customer with ID 1.
>> >
>> > This User B SQL works well too:
>> >
>> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> > select * from Customers where ID=1
>> >
>> > User B can read the dirty data.
>> >
>> > However, if user B goes to customer with ID 2, this will not work:
>> >
>> > update Customers set Name='My Customer Inc.' where ID=2
>> >
>> > It seems that the User A transaction is not applying a row-level lock,
>> > but rather a page lock, or even table lock. How can I force the UPDATE
>> > in user A's session to only lock one customer row?
>> >
>> >
>> >
>> >
>> >
>> > Uri Dimant wrote:
>> >> Davy
>> >> UPDATE statement creates an exclusive lock on the row so if the
>> >> another
>> >> user
>> >> wants to change/update the same row he/she will be locked, however
>> >> what
>> >> if
>> >> you have ( you probably have ) more details for the customer , so in
>> >> that
>> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> >> procedure
>> >> which makes updating
>> >>
>> >> Also take a look at transaction isolation levels:
>> >> a.. READ UNCOMMITTED
>> >> READ COMMITTED
>> >> REPEATABLE READ
>> >> SERIALIZABLE
>> >>
>> >>
>> >>
>> >> "Davy B" <david.bridge@.itv.com> wrote in message
>> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >> >I have a VB6 rich client application that accesses SQL Server 2000
>> >> > using ADO. The database has a table of customers. The application
>> >> > has
>> >> > a form that shows customer details, and allows the user to navigate
>> >> > the
>> >> > to next/previous customer in the table.
>> >> >
>> >> > When user A is the first to open customer 1's details, I want them
>> >> > to
>> >> > "take ownership" of that record, such that only user A can make
>> >> > changes
>> >> > to the record. When user B navigates to customer 1, I want user B
>> >> > to
>> >> > be able to view the customer's details, but not be able to change
>> >> > them.
>> >> >
>> >> > However, if user B navigates to customer 2, whose record is on the
>> >> > same
>> >> > data page as customer 1, I do want user B to be able to take
>> >> > ownership
>> >> > of customer 2's record, and be able to make changes.
>> >> >
>> >> > In short, I want row-level locking on the customers table. I was
>> >> > relying on the client updating a customer record to indicate they
>> >> > have
>> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
>> >> > two
>> >> > users view the same customer within five secs of each other, the
>> >> > second
>> >> > user cannot ascertain that the first user has taken ownership.
>> >> >
>> >> > How do I implement row-level locking? Or is there another way?
>> >> >
>> >
>|||Uri,
Yeah, that's my problem. I want user B to be able to save a change on
the second record, even though user A has got the first record locked.
I don't want user B to have to wait for user A to commit or roll back
their transaction.
I have used sp_lock, and established that the first update establishes
three locks if type TAB, PAG and KEY. I am starting to guess that the
behaviour I want is not possible - since the table has a primary key,
which is effectively a unique index, allowing two rows to be updated by
two users could compromise the index, so SQL Server prevents it.
If I continue with my current design, which is optimistic, I wonder
whether I could use files to indicate ownership? If all users have
access to a shared folder, the client application could open a WSH
textstream called C1.txt (where 1 = row ID), and leave it open. Anyone
who wants ownership of record 1 could attempt to create C1.txt - this
will fail if another user already has that file open. I'm thinking
laterally now?
Davy
Uri Dimant wrote:
> Dave
> I did just testing and it worked for me just fine, perhaps I missunderstood
> you.
> --Open connection1 (userA)
> --Run
> create table t (c1 int primary key,c2 char(1))
> insert into t values (1,'a')
> insert into t values (2,'b')
> begin tran
> update t set c2='c' where c1=1
> --rollback tran
> --Open connection2 (userB)
> --Run
> select * from t where c1 =2
> update t set c2='t' where c1=2
> --After I ssued ROLLBACK at connection1 i see
> c1 c2
> -- --
> 1 a
> 2 t
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> > Yes. It's the primary key. Is there an implication?
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> Do you have an index on ID column?
> >>
> >>
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> >> > Thanks, Uri. Most grateful.
> >> >
> >> > I still have a problem, though. I have set up two Query Analyzer
> >> > sessions, representing User A and User B. The first connection runs
> >> > this:
> >> >
> >> > begin tran
> >> > update Customers set Tel='01234 567 890' where ID=1
> >> >
> >> > Without committing the transaction, this SQL does apply an exclusive
> >> > lock on the customer with ID 1.
> >> >
> >> > This User B SQL works well too:
> >> >
> >> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> >> > select * from Customers where ID=1
> >> >
> >> > User B can read the dirty data.
> >> >
> >> > However, if user B goes to customer with ID 2, this will not work:
> >> >
> >> > update Customers set Name='My Customer Inc.' where ID=2
> >> >
> >> > It seems that the User A transaction is not applying a row-level lock,
> >> > but rather a page lock, or even table lock. How can I force the UPDATE
> >> > in user A's session to only lock one customer row?
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > Uri Dimant wrote:
> >> >> Davy
> >> >> UPDATE statement creates an exclusive lock on the row so if the
> >> >> another
> >> >> user
> >> >> wants to change/update the same row he/she will be locked, however
> >> >> what
> >> >> if
> >> >> you have ( you probably have ) more details for the customer , so in
> >> >> that
> >> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> >> procedure
> >> >> which makes updating
> >> >>
> >> >> Also take a look at transaction isolation levels:
> >> >> a.. READ UNCOMMITTED
> >> >> READ COMMITTED
> >> >> REPEATABLE READ
> >> >> SERIALIZABLE
> >> >>
> >> >>
> >> >>
> >> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> >> > using ADO. The database has a table of customers. The application
> >> >> > has
> >> >> > a form that shows customer details, and allows the user to navigate
> >> >> > the
> >> >> > to next/previous customer in the table.
> >> >> >
> >> >> > When user A is the first to open customer 1's details, I want them
> >> >> > to
> >> >> > "take ownership" of that record, such that only user A can make
> >> >> > changes
> >> >> > to the record. When user B navigates to customer 1, I want user B
> >> >> > to
> >> >> > be able to view the customer's details, but not be able to change
> >> >> > them.
> >> >> >
> >> >> > However, if user B navigates to customer 2, whose record is on the
> >> >> > same
> >> >> > data page as customer 1, I do want user B to be able to take
> >> >> > ownership
> >> >> > of customer 2's record, and be able to make changes.
> >> >> >
> >> >> > In short, I want row-level locking on the customers table. I was
> >> >> > relying on the client updating a customer record to indicate they
> >> >> > have
> >> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
> >> >> > two
> >> >> > users view the same customer within five secs of each other, the
> >> >> > second
> >> >> > user cannot ascertain that the first user has taken ownership.
> >> >> >
> >> >> > How do I implement row-level locking? Or is there another way?
> >> >> >
> >> >
> >|||Thanks for getting involved, Mike.
It now seems clear that if SQL Server was to allow two users to lock
and update different rows in the same table, the primary key could get
compromised. So it must be preventing me from achieving a row-level
lock in the interest of maintaining integrity.
My current client design is optimistic, but the users are now having
problems whereby two of them do occasionally open the same record. The
first makes changes which get overwritten by the second. When I change
the design to pessimistic, only the first user who gets an exclusive
lock is able to save changes to any record in that same table. The
others can do a dirty read, but it's effectively view-only. I am now
looking at a design that retains optimism, and uses file locks - if
user A opens record 123, they attempt to create a file called 123.txt
in a shared folder. The file creation will fail if another user has
already opened record 123.
Maybe there's a better way?
Davy
Michael Epprecht [MSFT] wrote:
> Hi
> Row level locking would be occurring at data level, but if a modification of
> an index is involved, you might have blocking on the index page.
> Look at the output of sp_lock to see what type of lock is causing the
> problem.
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Yes, Dan. My current design is optimistic, which is fine 99% of the
time. But occasionally two users do open the same record; one user
overwrites changes made by another. The problem with pessimistic
design is that when one user opts to change a record it precludes all
others from changing one. The whole table gets locked.
I am considering continuing with an optimistic design, but using files
created in a shared area to indicate ownership. If user A opens record
123 for modification, they will create and lock a file called 123.txt.
When user B access that record they attempt to create a 123.txt, but
get "access denied", since user A has the file locked. Lateral
thinking? Or should I be doing this a different way?
Davy
Dan Guzman wrote:
> You've described a 'pessimistic concurrency' technique. Here's an
> description from the SQL Server 2005 Books Online:
> <Excerpt
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> Concurrency control theory has two classifications for the methods of
> instituting concurrency control:
> Pessimistic concurrency control
> A system of locks prevents users from modifying data in a way that affects
> other users. After a user performs an action that causes a lock to be
> applied, other users cannot perform actions that would conflict with the
> lock until the owner releases it. This is called pessimistic control because
> it is mainly used in environments where there is high contention for data,
> where the cost of protecting data with locks is less than the cost of
> rolling back transactions if concurrency conflicts occur.
> Optimistic concurrency control
> In optimistic concurrency control, users do not lock data when they read it.
> When a user updates data, the system checks to see if another user changed
> the data after it was read. If another user updated the data, an error is
> raised. Typically, the user receiving the error rolls back the transaction
> and starts over. This is called optimistic because it is mainly used in
> environments where there is low contention for data, and where the cost of
> occasionally rolling back a transaction outweighs the costs of locking data
> when read.
> </Excerpt>
> Although you could use SQL Server's row locking to implement pessimistic
> concurrency, that would be especially bad for your application because it
> would require that you keep the transaction open while waiting for user
> action. Consider that the user may go to lunch or take the afternoon off
> while viewing customer data. A cardinal rule is that one should never keep
> a transaction open while waiting for user action.
> I suggest you consider using an optimistic concurrency approach instead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Hi
What a lot of developers do it to use row versioning. This version stamp is
read and when updating the row, you first compare what is on the row
compared to what you have read earlier. If they are the same, the update may
proceed, if they differ, then someone else has made a change and the update
fails.
The SQL Server data type timestamp is used for this.
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||On 6 Jun 2006 07:31:02 -0700, "Davy B" <david.bridge@.itv.com> wrote:
>It now seems clear that if SQL Server was to allow two users to lock
>and update different rows in the same table, the primary key could get
>compromised. So it must be preventing me from achieving a row-level
>lock in the interest of maintaining integrity.
>My current client design is optimistic, but the users are now having
>problems whereby two of them do occasionally open the same record. The
>first makes changes which get overwritten by the second. When I change
>the design to pessimistic, only the first user who gets an exclusive
>lock is able to save changes to any record in that same table. The
>others can do a dirty read, but it's effectively view-only. I am now
>looking at a design that retains optimism, and uses file locks - if
>user A opens record 123, they attempt to create a file called 123.txt
>in a shared folder. The file creation will fail if another user has
>already opened record 123.
>Maybe there's a better way?
Not really.
If you do that file-lock business, second user will still be locked
out! It's the logical situation, not the mechanism, which is the
problem.
Anyway, why use a file, create your own lock table, if you really want
to go that way. Which you probably shouldn't.
My advice is to fiddle with the ADO/SQLServer features for pessimistic
locking a little further. Set the lock_timeout to a small number so
the second guy to write can get a quick message that the record is
locked. Better yet, warn the second guy before he modifies anything,
that the record is already locked! Boy, this optimistic locking is a
lost art, ain't it?
Josh|||> But occasionally two users do open the same record; one user
> overwrites changes made by another.
That's chaos (last in wins) rather than optimistic concurrency. To properly
implement optimistic concurrency you need to check to ensure initially
retrieved data haven't changed and notify the user accordingly.
As Mike suggested, you can simplify coding by checking a rowversion column
(a.k.a timestamp) during the update rather than specifying all columns to be
updated. SQL Server generates a unique value for the rowversion each time
the row is updated so the check will fail of the row has changed since
initially read by the application. Below is an example.
CREATE TABLE Table1
(
Table1_Key int NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
Table1_Data int NOT NULL,
RowVersionColumn RowVersion NOT NULL
)
GO
ALTER PROC dbo.usp_Update_Table1
@.Table1_Key int,
@.Table1_Data int,
@.ExpectedRowVersion rowversion
AS
SET NOCOUNT ON
UPDATE dbo.Table1
SET @.Table1_Data = @.Table1_Data
WHERE
Table1_Key = @.Table1_Key AND
RowVersionColumn = @.ExpectedRowVersion
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data updated or deleted by another user', 16, 1)
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||It does help, Dan. I am most grateful.
Dan Guzman wrote:
> > But occasionally two users do open the same record; one user
> > overwrites changes made by another.
> That's chaos (last in wins) rather than optimistic concurrency. To properly
> implement optimistic concurrency you need to check to ensure initially
> retrieved data haven't changed and notify the user accordingly.
> As Mike suggested, you can simplify coding by checking a rowversion column
> (a.k.a timestamp) during the update rather than specifying all columns to be
> updated. SQL Server generates a unique value for the rowversion each time
> the row is updated so the check will fail of the row has changed since
> initially read by the application. Below is an example.
> CREATE TABLE Table1
> (
> Table1_Key int NOT NULL
> CONSTRAINT PK_Table1 PRIMARY KEY,
> Table1_Data int NOT NULL,
> RowVersionColumn RowVersion NOT NULL
> )
> GO
> ALTER PROC dbo.usp_Update_Table1
> @.Table1_Key int,
> @.Table1_Data int,
> @.ExpectedRowVersion rowversion
> AS
> SET NOCOUNT ON
> UPDATE dbo.Table1
> SET @.Table1_Data = @.Table1_Data
> WHERE
> Table1_Key = @.Table1_Key AND
> RowVersionColumn = @.ExpectedRowVersion
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data updated or deleted by another user', 16, 1)
> END
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> > Yes, Dan. My current design is optimistic, which is fine 99% of the
> > time. But occasionally two users do open the same record; one user
> > overwrites changes made by another. The problem with pessimistic
> > design is that when one user opts to change a record it precludes all
> > others from changing one. The whole table gets locked.
> >
> > I am considering continuing with an optimistic design, but using files
> > created in a shared area to indicate ownership. If user A opens record
> > 123 for modification, they will create and lock a file called 123.txt.
> > When user B access that record they attempt to create a 123.txt, but
> > get "access denied", since user A has the file locked. Lateral
> > thinking? Or should I be doing this a different way?
> >
> > Davy
> >
> >
> >
> >
> > Dan Guzman wrote:
> >> You've described a 'pessimistic concurrency' technique. Here's an
> >> description from the SQL Server 2005 Books Online:
> >>
> >> <Excerpt
> >> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> >>
> >> Concurrency control theory has two classifications for the methods of
> >> instituting concurrency control:
> >>
> >> Pessimistic concurrency control
> >>
> >> A system of locks prevents users from modifying data in a way that
> >> affects
> >> other users. After a user performs an action that causes a lock to be
> >> applied, other users cannot perform actions that would conflict with the
> >> lock until the owner releases it. This is called pessimistic control
> >> because
> >> it is mainly used in environments where there is high contention for
> >> data,
> >> where the cost of protecting data with locks is less than the cost of
> >> rolling back transactions if concurrency conflicts occur.
> >>
> >> Optimistic concurrency control
> >>
> >> In optimistic concurrency control, users do not lock data when they read
> >> it.
> >> When a user updates data, the system checks to see if another user
> >> changed
> >> the data after it was read. If another user updated the data, an error is
> >> raised. Typically, the user receiving the error rolls back the
> >> transaction
> >> and starts over. This is called optimistic because it is mainly used in
> >> environments where there is low contention for data, and where the cost
> >> of
> >> occasionally rolling back a transaction outweighs the costs of locking
> >> data
> >> when read.
> >>
> >> </Excerpt>
> >>
> >> Although you could use SQL Server's row locking to implement pessimistic
> >> concurrency, that would be especially bad for your application because it
> >> would require that you keep the transaction open while waiting for user
> >> action. Consider that the user may go to lunch or take the afternoon off
> >> while viewing customer data. A cardinal rule is that one should never
> >> keep
> >> a transaction open while waiting for user action.
> >>
> >> I suggest you consider using an optimistic concurrency approach instead.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >
using ADO. The database has a table of customers. The application has
a form that shows customer details, and allows the user to navigate the
to next/previous customer in the table.
When user A is the first to open customer 1's details, I want them to
"take ownership" of that record, such that only user A can make changes
to the record. When user B navigates to customer 1, I want user B to
be able to view the customer's details, but not be able to change them.
However, if user B navigates to customer 2, whose record is on the same
data page as customer 1, I do want user B to be able to take ownership
of customer 2's record, and be able to make changes.
In short, I want row-level locking on the customers table. I was
relying on the client updating a customer record to indicate they have
ownership, but ADO caches changes for (I believe) 5 seconds, so if two
users view the same customer within five secs of each other, the second
user cannot ascertain that the first user has taken ownership.
How do I implement row-level locking? Or is there another way?Davy
UPDATE statement creates an exclusive lock on the row so if the another user
wants to change/update the same row he/she will be locked, however what if
you have ( you probably have ) more details for the customer , so in that
case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
which makes updating
Also take a look at transaction isolation levels:
a.. READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Thanks, Uri. Most grateful.
I still have a problem, though. I have set up two Query Analyzer
sessions, representing User A and User B. The first connection runs
this:
begin tran
update Customers set Tel='01234 567 890' where ID=1
Without committing the transaction, this SQL does apply an exclusive
lock on the customer with ID 1.
This User B SQL works well too:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from Customers where ID=1
User B can read the dirty data.
However, if user B goes to customer with ID 2, this will not work:
update Customers set Name='My Customer Inc.' where ID=2
It seems that the User A transaction is not applying a row-level lock,
but rather a page lock, or even table lock. How can I force the UPDATE
in user A's session to only lock one customer row?
Uri Dimant wrote:
> Davy
> UPDATE statement creates an exclusive lock on the row so if the another user
> wants to change/update the same row he/she will be locked, however what if
> you have ( you probably have ) more details for the customer , so in that
> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
> which makes updating
> Also take a look at transaction isolation levels:
> a.. READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Davy
Do you have an index on ID column?
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||Yes. It's the primary key. Is there an implication?
Uri Dimant wrote:
> Davy
> Do you have an index on ID column?
>
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Hi
Row level locking would be occurring at data level, but if a modification of
an index is involved, you might have blocking on the index page.
Look at the output of sp_lock to see what type of lock is causing the
problem.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||You've described a 'pessimistic concurrency' technique. Here's an
description from the SQL Server 2005 Books Online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
Concurrency control theory has two classifications for the methods of
instituting concurrency control:
Pessimistic concurrency control
A system of locks prevents users from modifying data in a way that affects
other users. After a user performs an action that causes a lock to be
applied, other users cannot perform actions that would conflict with the
lock until the owner releases it. This is called pessimistic control because
it is mainly used in environments where there is high contention for data,
where the cost of protecting data with locks is less than the cost of
rolling back transactions if concurrency conflicts occur.
Optimistic concurrency control
In optimistic concurrency control, users do not lock data when they read it.
When a user updates data, the system checks to see if another user changed
the data after it was read. If another user updated the data, an error is
raised. Typically, the user receiving the error rolls back the transaction
and starts over. This is called optimistic because it is mainly used in
environments where there is low contention for data, and where the cost of
occasionally rolling back a transaction outweighs the costs of locking data
when read.
</Excerpt>
Although you could use SQL Server's row locking to implement pessimistic
concurrency, that would be especially bad for your application because it
would require that you keep the transaction open while waiting for user
action. Consider that the user may go to lunch or take the afternoon off
while viewing customer data. A cardinal rule is that one should never keep
a transaction open while waiting for user action.
I suggest you consider using an optimistic concurrency approach instead.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Dave
I did just testing and it worked for me just fine, perhaps I missunderstood
you.
--Open connection1 (userA)
--Run
create table t (c1 int primary key,c2 char(1))
insert into t values (1,'a')
insert into t values (2,'b')
begin tran
update t set c2='c' where c1=1
--rollback tran
--Open connection2 (userB)
--Run
select * from t where c1 =2
update t set c2='t' where c1=2
--After I ssued ROLLBACK at connection1 i see
c1 c2
-- --
1 a
2 t
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> Yes. It's the primary key. Is there an implication?
>
> Uri Dimant wrote:
>> Davy
>> Do you have an index on ID column?
>>
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
>> > Thanks, Uri. Most grateful.
>> >
>> > I still have a problem, though. I have set up two Query Analyzer
>> > sessions, representing User A and User B. The first connection runs
>> > this:
>> >
>> > begin tran
>> > update Customers set Tel='01234 567 890' where ID=1
>> >
>> > Without committing the transaction, this SQL does apply an exclusive
>> > lock on the customer with ID 1.
>> >
>> > This User B SQL works well too:
>> >
>> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> > select * from Customers where ID=1
>> >
>> > User B can read the dirty data.
>> >
>> > However, if user B goes to customer with ID 2, this will not work:
>> >
>> > update Customers set Name='My Customer Inc.' where ID=2
>> >
>> > It seems that the User A transaction is not applying a row-level lock,
>> > but rather a page lock, or even table lock. How can I force the UPDATE
>> > in user A's session to only lock one customer row?
>> >
>> >
>> >
>> >
>> >
>> > Uri Dimant wrote:
>> >> Davy
>> >> UPDATE statement creates an exclusive lock on the row so if the
>> >> another
>> >> user
>> >> wants to change/update the same row he/she will be locked, however
>> >> what
>> >> if
>> >> you have ( you probably have ) more details for the customer , so in
>> >> that
>> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> >> procedure
>> >> which makes updating
>> >>
>> >> Also take a look at transaction isolation levels:
>> >> a.. READ UNCOMMITTED
>> >> READ COMMITTED
>> >> REPEATABLE READ
>> >> SERIALIZABLE
>> >>
>> >>
>> >>
>> >> "Davy B" <david.bridge@.itv.com> wrote in message
>> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >> >I have a VB6 rich client application that accesses SQL Server 2000
>> >> > using ADO. The database has a table of customers. The application
>> >> > has
>> >> > a form that shows customer details, and allows the user to navigate
>> >> > the
>> >> > to next/previous customer in the table.
>> >> >
>> >> > When user A is the first to open customer 1's details, I want them
>> >> > to
>> >> > "take ownership" of that record, such that only user A can make
>> >> > changes
>> >> > to the record. When user B navigates to customer 1, I want user B
>> >> > to
>> >> > be able to view the customer's details, but not be able to change
>> >> > them.
>> >> >
>> >> > However, if user B navigates to customer 2, whose record is on the
>> >> > same
>> >> > data page as customer 1, I do want user B to be able to take
>> >> > ownership
>> >> > of customer 2's record, and be able to make changes.
>> >> >
>> >> > In short, I want row-level locking on the customers table. I was
>> >> > relying on the client updating a customer record to indicate they
>> >> > have
>> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
>> >> > two
>> >> > users view the same customer within five secs of each other, the
>> >> > second
>> >> > user cannot ascertain that the first user has taken ownership.
>> >> >
>> >> > How do I implement row-level locking? Or is there another way?
>> >> >
>> >
>|||Uri,
Yeah, that's my problem. I want user B to be able to save a change on
the second record, even though user A has got the first record locked.
I don't want user B to have to wait for user A to commit or roll back
their transaction.
I have used sp_lock, and established that the first update establishes
three locks if type TAB, PAG and KEY. I am starting to guess that the
behaviour I want is not possible - since the table has a primary key,
which is effectively a unique index, allowing two rows to be updated by
two users could compromise the index, so SQL Server prevents it.
If I continue with my current design, which is optimistic, I wonder
whether I could use files to indicate ownership? If all users have
access to a shared folder, the client application could open a WSH
textstream called C1.txt (where 1 = row ID), and leave it open. Anyone
who wants ownership of record 1 could attempt to create C1.txt - this
will fail if another user already has that file open. I'm thinking
laterally now?
Davy
Uri Dimant wrote:
> Dave
> I did just testing and it worked for me just fine, perhaps I missunderstood
> you.
> --Open connection1 (userA)
> --Run
> create table t (c1 int primary key,c2 char(1))
> insert into t values (1,'a')
> insert into t values (2,'b')
> begin tran
> update t set c2='c' where c1=1
> --rollback tran
> --Open connection2 (userB)
> --Run
> select * from t where c1 =2
> update t set c2='t' where c1=2
> --After I ssued ROLLBACK at connection1 i see
> c1 c2
> -- --
> 1 a
> 2 t
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> > Yes. It's the primary key. Is there an implication?
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> Do you have an index on ID column?
> >>
> >>
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> >> > Thanks, Uri. Most grateful.
> >> >
> >> > I still have a problem, though. I have set up two Query Analyzer
> >> > sessions, representing User A and User B. The first connection runs
> >> > this:
> >> >
> >> > begin tran
> >> > update Customers set Tel='01234 567 890' where ID=1
> >> >
> >> > Without committing the transaction, this SQL does apply an exclusive
> >> > lock on the customer with ID 1.
> >> >
> >> > This User B SQL works well too:
> >> >
> >> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> >> > select * from Customers where ID=1
> >> >
> >> > User B can read the dirty data.
> >> >
> >> > However, if user B goes to customer with ID 2, this will not work:
> >> >
> >> > update Customers set Name='My Customer Inc.' where ID=2
> >> >
> >> > It seems that the User A transaction is not applying a row-level lock,
> >> > but rather a page lock, or even table lock. How can I force the UPDATE
> >> > in user A's session to only lock one customer row?
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > Uri Dimant wrote:
> >> >> Davy
> >> >> UPDATE statement creates an exclusive lock on the row so if the
> >> >> another
> >> >> user
> >> >> wants to change/update the same row he/she will be locked, however
> >> >> what
> >> >> if
> >> >> you have ( you probably have ) more details for the customer , so in
> >> >> that
> >> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> >> procedure
> >> >> which makes updating
> >> >>
> >> >> Also take a look at transaction isolation levels:
> >> >> a.. READ UNCOMMITTED
> >> >> READ COMMITTED
> >> >> REPEATABLE READ
> >> >> SERIALIZABLE
> >> >>
> >> >>
> >> >>
> >> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> >> > using ADO. The database has a table of customers. The application
> >> >> > has
> >> >> > a form that shows customer details, and allows the user to navigate
> >> >> > the
> >> >> > to next/previous customer in the table.
> >> >> >
> >> >> > When user A is the first to open customer 1's details, I want them
> >> >> > to
> >> >> > "take ownership" of that record, such that only user A can make
> >> >> > changes
> >> >> > to the record. When user B navigates to customer 1, I want user B
> >> >> > to
> >> >> > be able to view the customer's details, but not be able to change
> >> >> > them.
> >> >> >
> >> >> > However, if user B navigates to customer 2, whose record is on the
> >> >> > same
> >> >> > data page as customer 1, I do want user B to be able to take
> >> >> > ownership
> >> >> > of customer 2's record, and be able to make changes.
> >> >> >
> >> >> > In short, I want row-level locking on the customers table. I was
> >> >> > relying on the client updating a customer record to indicate they
> >> >> > have
> >> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
> >> >> > two
> >> >> > users view the same customer within five secs of each other, the
> >> >> > second
> >> >> > user cannot ascertain that the first user has taken ownership.
> >> >> >
> >> >> > How do I implement row-level locking? Or is there another way?
> >> >> >
> >> >
> >|||Thanks for getting involved, Mike.
It now seems clear that if SQL Server was to allow two users to lock
and update different rows in the same table, the primary key could get
compromised. So it must be preventing me from achieving a row-level
lock in the interest of maintaining integrity.
My current client design is optimistic, but the users are now having
problems whereby two of them do occasionally open the same record. The
first makes changes which get overwritten by the second. When I change
the design to pessimistic, only the first user who gets an exclusive
lock is able to save changes to any record in that same table. The
others can do a dirty read, but it's effectively view-only. I am now
looking at a design that retains optimism, and uses file locks - if
user A opens record 123, they attempt to create a file called 123.txt
in a shared folder. The file creation will fail if another user has
already opened record 123.
Maybe there's a better way?
Davy
Michael Epprecht [MSFT] wrote:
> Hi
> Row level locking would be occurring at data level, but if a modification of
> an index is involved, you might have blocking on the index page.
> Look at the output of sp_lock to see what type of lock is causing the
> problem.
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Yes, Dan. My current design is optimistic, which is fine 99% of the
time. But occasionally two users do open the same record; one user
overwrites changes made by another. The problem with pessimistic
design is that when one user opts to change a record it precludes all
others from changing one. The whole table gets locked.
I am considering continuing with an optimistic design, but using files
created in a shared area to indicate ownership. If user A opens record
123 for modification, they will create and lock a file called 123.txt.
When user B access that record they attempt to create a 123.txt, but
get "access denied", since user A has the file locked. Lateral
thinking? Or should I be doing this a different way?
Davy
Dan Guzman wrote:
> You've described a 'pessimistic concurrency' technique. Here's an
> description from the SQL Server 2005 Books Online:
> <Excerpt
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> Concurrency control theory has two classifications for the methods of
> instituting concurrency control:
> Pessimistic concurrency control
> A system of locks prevents users from modifying data in a way that affects
> other users. After a user performs an action that causes a lock to be
> applied, other users cannot perform actions that would conflict with the
> lock until the owner releases it. This is called pessimistic control because
> it is mainly used in environments where there is high contention for data,
> where the cost of protecting data with locks is less than the cost of
> rolling back transactions if concurrency conflicts occur.
> Optimistic concurrency control
> In optimistic concurrency control, users do not lock data when they read it.
> When a user updates data, the system checks to see if another user changed
> the data after it was read. If another user updated the data, an error is
> raised. Typically, the user receiving the error rolls back the transaction
> and starts over. This is called optimistic because it is mainly used in
> environments where there is low contention for data, and where the cost of
> occasionally rolling back a transaction outweighs the costs of locking data
> when read.
> </Excerpt>
> Although you could use SQL Server's row locking to implement pessimistic
> concurrency, that would be especially bad for your application because it
> would require that you keep the transaction open while waiting for user
> action. Consider that the user may go to lunch or take the afternoon off
> while viewing customer data. A cardinal rule is that one should never keep
> a transaction open while waiting for user action.
> I suggest you consider using an optimistic concurrency approach instead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Hi
What a lot of developers do it to use row versioning. This version stamp is
read and when updating the row, you first compare what is on the row
compared to what you have read earlier. If they are the same, the update may
proceed, if they differ, then someone else has made a change and the update
fails.
The SQL Server data type timestamp is used for this.
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||On 6 Jun 2006 07:31:02 -0700, "Davy B" <david.bridge@.itv.com> wrote:
>It now seems clear that if SQL Server was to allow two users to lock
>and update different rows in the same table, the primary key could get
>compromised. So it must be preventing me from achieving a row-level
>lock in the interest of maintaining integrity.
>My current client design is optimistic, but the users are now having
>problems whereby two of them do occasionally open the same record. The
>first makes changes which get overwritten by the second. When I change
>the design to pessimistic, only the first user who gets an exclusive
>lock is able to save changes to any record in that same table. The
>others can do a dirty read, but it's effectively view-only. I am now
>looking at a design that retains optimism, and uses file locks - if
>user A opens record 123, they attempt to create a file called 123.txt
>in a shared folder. The file creation will fail if another user has
>already opened record 123.
>Maybe there's a better way?
Not really.
If you do that file-lock business, second user will still be locked
out! It's the logical situation, not the mechanism, which is the
problem.
Anyway, why use a file, create your own lock table, if you really want
to go that way. Which you probably shouldn't.
My advice is to fiddle with the ADO/SQLServer features for pessimistic
locking a little further. Set the lock_timeout to a small number so
the second guy to write can get a quick message that the record is
locked. Better yet, warn the second guy before he modifies anything,
that the record is already locked! Boy, this optimistic locking is a
lost art, ain't it?
Josh|||> But occasionally two users do open the same record; one user
> overwrites changes made by another.
That's chaos (last in wins) rather than optimistic concurrency. To properly
implement optimistic concurrency you need to check to ensure initially
retrieved data haven't changed and notify the user accordingly.
As Mike suggested, you can simplify coding by checking a rowversion column
(a.k.a timestamp) during the update rather than specifying all columns to be
updated. SQL Server generates a unique value for the rowversion each time
the row is updated so the check will fail of the row has changed since
initially read by the application. Below is an example.
CREATE TABLE Table1
(
Table1_Key int NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
Table1_Data int NOT NULL,
RowVersionColumn RowVersion NOT NULL
)
GO
ALTER PROC dbo.usp_Update_Table1
@.Table1_Key int,
@.Table1_Data int,
@.ExpectedRowVersion rowversion
AS
SET NOCOUNT ON
UPDATE dbo.Table1
SET @.Table1_Data = @.Table1_Data
WHERE
Table1_Key = @.Table1_Key AND
RowVersionColumn = @.ExpectedRowVersion
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data updated or deleted by another user', 16, 1)
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||It does help, Dan. I am most grateful.
Dan Guzman wrote:
> > But occasionally two users do open the same record; one user
> > overwrites changes made by another.
> That's chaos (last in wins) rather than optimistic concurrency. To properly
> implement optimistic concurrency you need to check to ensure initially
> retrieved data haven't changed and notify the user accordingly.
> As Mike suggested, you can simplify coding by checking a rowversion column
> (a.k.a timestamp) during the update rather than specifying all columns to be
> updated. SQL Server generates a unique value for the rowversion each time
> the row is updated so the check will fail of the row has changed since
> initially read by the application. Below is an example.
> CREATE TABLE Table1
> (
> Table1_Key int NOT NULL
> CONSTRAINT PK_Table1 PRIMARY KEY,
> Table1_Data int NOT NULL,
> RowVersionColumn RowVersion NOT NULL
> )
> GO
> ALTER PROC dbo.usp_Update_Table1
> @.Table1_Key int,
> @.Table1_Data int,
> @.ExpectedRowVersion rowversion
> AS
> SET NOCOUNT ON
> UPDATE dbo.Table1
> SET @.Table1_Data = @.Table1_Data
> WHERE
> Table1_Key = @.Table1_Key AND
> RowVersionColumn = @.ExpectedRowVersion
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data updated or deleted by another user', 16, 1)
> END
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> > Yes, Dan. My current design is optimistic, which is fine 99% of the
> > time. But occasionally two users do open the same record; one user
> > overwrites changes made by another. The problem with pessimistic
> > design is that when one user opts to change a record it precludes all
> > others from changing one. The whole table gets locked.
> >
> > I am considering continuing with an optimistic design, but using files
> > created in a shared area to indicate ownership. If user A opens record
> > 123 for modification, they will create and lock a file called 123.txt.
> > When user B access that record they attempt to create a 123.txt, but
> > get "access denied", since user A has the file locked. Lateral
> > thinking? Or should I be doing this a different way?
> >
> > Davy
> >
> >
> >
> >
> > Dan Guzman wrote:
> >> You've described a 'pessimistic concurrency' technique. Here's an
> >> description from the SQL Server 2005 Books Online:
> >>
> >> <Excerpt
> >> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> >>
> >> Concurrency control theory has two classifications for the methods of
> >> instituting concurrency control:
> >>
> >> Pessimistic concurrency control
> >>
> >> A system of locks prevents users from modifying data in a way that
> >> affects
> >> other users. After a user performs an action that causes a lock to be
> >> applied, other users cannot perform actions that would conflict with the
> >> lock until the owner releases it. This is called pessimistic control
> >> because
> >> it is mainly used in environments where there is high contention for
> >> data,
> >> where the cost of protecting data with locks is less than the cost of
> >> rolling back transactions if concurrency conflicts occur.
> >>
> >> Optimistic concurrency control
> >>
> >> In optimistic concurrency control, users do not lock data when they read
> >> it.
> >> When a user updates data, the system checks to see if another user
> >> changed
> >> the data after it was read. If another user updated the data, an error is
> >> raised. Typically, the user receiving the error rolls back the
> >> transaction
> >> and starts over. This is called optimistic because it is mainly used in
> >> environments where there is low contention for data, and where the cost
> >> of
> >> occasionally rolling back a transaction outweighs the costs of locking
> >> data
> >> when read.
> >>
> >> </Excerpt>
> >>
> >> Although you could use SQL Server's row locking to implement pessimistic
> >> concurrency, that would be especially bad for your application because it
> >> would require that you keep the transaction open while waiting for user
> >> action. Consider that the user may go to lunch or take the afternoon off
> >> while viewing customer data. A cardinal rule is that one should never
> >> keep
> >> a transaction open while waiting for user action.
> >>
> >> I suggest you consider using an optimistic concurrency approach instead.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >
Tuesday, March 20, 2012
Row Insertion Error
I am writing code to interface with a pre-existing and unchangeable dBase
database that we access through ODBC. This database has column names of the
format "NAME$0101" and "NAME$0102" etc. These column names work fine for
retrieving and updating data via SELECT and UPDATE, but when I try to insert
a new row via INSERT INTO I get the error:
The INSERT INTO statement contains the following unknown field name:
'TRACK$0101'. Make sure you have typed the name correctly, and try the
operation again.
If I create an access database, link to the dbase file, and use the
Microsoft Access ODBC driver instead of the dBase driver, this query works
fine. I have tried all combinations of surrounding the column name in [
] and
"" and ``.
Any ideas?
IanIf no one else responds, perhaps you can get a response from the 'dBase'
discssion alias.
If you get a sloution can you post a response here? This is a very
interesting bug.
--
| Thread-Topic: Row Insertion Error
| thread-index: AcWjacAZLKPUNA2hTIaxHhhgvdybmQ==
| X-WBNR-Posting-Host: 67.20.172.139
| From: "examnotes"
<IanMascomm@.discussions.microsoft.com>
| Subject: Row Insertion Error
| Date: Wed, 17 Aug 2005 13:25:03 -0700
| Lines: 18
| Message-ID: <55CE13A6-1DB1-4F5C-BB40-2C290FD53343@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2595
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I am writing code to interface with a pre-existing and unchangeable dBase
| database that we access through ODBC. This database has column names of
the
| format "NAME$0101" and "NAME$0102" etc. These column names work fine for
| retrieving and updating data via SELECT and UPDATE, but when I try to
insert
| a new row via INSERT INTO I get the error:
|
| The INSERT INTO statement contains the following unknown field name:
| 'TRACK$0101'. Make sure you have typed the name correctly, and try the
| operation again.
|
| If I create an access database, link to the dbase file, and use the
| Microsoft Access ODBC driver instead of the dBase driver, this query
works
| fine. I have tried all combinations of surrounding the column name in
1;]
and
| "" and ``.
|
| Any ideas?
|
| Ian
|
database that we access through ODBC. This database has column names of the
format "NAME$0101" and "NAME$0102" etc. These column names work fine for
retrieving and updating data via SELECT and UPDATE, but when I try to insert
a new row via INSERT INTO I get the error:
The INSERT INTO statement contains the following unknown field name:
'TRACK$0101'. Make sure you have typed the name correctly, and try the
operation again.
If I create an access database, link to the dbase file, and use the
Microsoft Access ODBC driver instead of the dBase driver, this query works
fine. I have tried all combinations of surrounding the column name in [
] and
"" and ``.
Any ideas?
IanIf no one else responds, perhaps you can get a response from the 'dBase'
discssion alias.
If you get a sloution can you post a response here? This is a very
interesting bug.
--
| Thread-Topic: Row Insertion Error
| thread-index: AcWjacAZLKPUNA2hTIaxHhhgvdybmQ==
| X-WBNR-Posting-Host: 67.20.172.139
| From: "examnotes"
<IanMascomm@.discussions.microsoft.com>
| Subject: Row Insertion Error
| Date: Wed, 17 Aug 2005 13:25:03 -0700
| Lines: 18
| Message-ID: <55CE13A6-1DB1-4F5C-BB40-2C290FD53343@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2595
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I am writing code to interface with a pre-existing and unchangeable dBase
| database that we access through ODBC. This database has column names of
the
| format "NAME$0101" and "NAME$0102" etc. These column names work fine for
| retrieving and updating data via SELECT and UPDATE, but when I try to
insert
| a new row via INSERT INTO I get the error:
|
| The INSERT INTO statement contains the following unknown field name:
| 'TRACK$0101'. Make sure you have typed the name correctly, and try the
| operation again.
|
| If I create an access database, link to the dbase file, and use the
| Microsoft Access ODBC driver instead of the dBase driver, this query
works
| fine. I have tried all combinations of surrounding the column name in
1;]
and
| "" and ``.
|
| Any ideas?
|
| Ian
|
Row Insertion Error
I am writing code to interface with a pre-existing and unchangeable dBase
database that we access through ODBC. This database has column names of the
format "NAME$0101" and "NAME$0102" etc. These column names work fine for
retrieving and updating data via SELECT and UPDATE, but when I try to insert
a new row via INSERT INTO I get the error:
The INSERT INTO statement contains the following unknown field name:
'TRACK$0101'. Make sure you have typed the name correctly, and try the
operation again.
If I create an access database, link to the dbase file, and use the
Microsoft Access ODBC driver instead of the dBase driver, this query works
fine. I have tried all combinations of surrounding the column name in [] and
"" and ``.
Any ideas?
Ian
If no one else responds, perhaps you can get a response from the 'dBase'
discssion alias.
If you get a sloution can you post a response here? This is a very
interesting bug.
| Thread-Topic: Row Insertion Error
| thread-index: AcWjacAZLKPUNA2hTIaxHhhgvdybmQ==
| X-WBNR-Posting-Host: 67.20.172.139
| From: "=?Utf-8?B?SWFuTWFzY29tbQ==?="
<IanMascomm@.discussions.microsoft.com>
| Subject: Row Insertion Error
| Date: Wed, 17 Aug 2005 13:25:03 -0700
| Lines: 18
| Message-ID: <55CE13A6-1DB1-4F5C-BB40-2C290FD53343@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2595
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I am writing code to interface with a pre-existing and unchangeable dBase
| database that we access through ODBC. This database has column names of
the
| format "NAME$0101" and "NAME$0102" etc. These column names work fine for
| retrieving and updating data via SELECT and UPDATE, but when I try to
insert
| a new row via INSERT INTO I get the error:
|
| The INSERT INTO statement contains the following unknown field name:
| 'TRACK$0101'. Make sure you have typed the name correctly, and try the
| operation again.
|
| If I create an access database, link to the dbase file, and use the
| Microsoft Access ODBC driver instead of the dBase driver, this query
works
| fine. I have tried all combinations of surrounding the column name in []
and
| "" and ``.
|
| Any ideas?
|
| Ian
|
database that we access through ODBC. This database has column names of the
format "NAME$0101" and "NAME$0102" etc. These column names work fine for
retrieving and updating data via SELECT and UPDATE, but when I try to insert
a new row via INSERT INTO I get the error:
The INSERT INTO statement contains the following unknown field name:
'TRACK$0101'. Make sure you have typed the name correctly, and try the
operation again.
If I create an access database, link to the dbase file, and use the
Microsoft Access ODBC driver instead of the dBase driver, this query works
fine. I have tried all combinations of surrounding the column name in [] and
"" and ``.
Any ideas?
Ian
If no one else responds, perhaps you can get a response from the 'dBase'
discssion alias.
If you get a sloution can you post a response here? This is a very
interesting bug.
| Thread-Topic: Row Insertion Error
| thread-index: AcWjacAZLKPUNA2hTIaxHhhgvdybmQ==
| X-WBNR-Posting-Host: 67.20.172.139
| From: "=?Utf-8?B?SWFuTWFzY29tbQ==?="
<IanMascomm@.discussions.microsoft.com>
| Subject: Row Insertion Error
| Date: Wed, 17 Aug 2005 13:25:03 -0700
| Lines: 18
| Message-ID: <55CE13A6-1DB1-4F5C-BB40-2C290FD53343@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2595
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I am writing code to interface with a pre-existing and unchangeable dBase
| database that we access through ODBC. This database has column names of
the
| format "NAME$0101" and "NAME$0102" etc. These column names work fine for
| retrieving and updating data via SELECT and UPDATE, but when I try to
insert
| a new row via INSERT INTO I get the error:
|
| The INSERT INTO statement contains the following unknown field name:
| 'TRACK$0101'. Make sure you have typed the name correctly, and try the
| operation again.
|
| If I create an access database, link to the dbase file, and use the
| Microsoft Access ODBC driver instead of the dBase driver, this query
works
| fine. I have tried all combinations of surrounding the column name in []
and
| "" and ``.
|
| Any ideas?
|
| Ian
|
Friday, March 9, 2012
Row based security
Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Row based security
Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:
> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>
Subscribe to:
Posts (Atom)