locking problems with read only users

[roswald]

We give our users read only access to the reporting repository on SQL Server 2000.
Can you tell me how a user with this type of access could ever get any type of lock on a table, page or database?

Also, if a read only user is in a table will it prevent a program that has write privleges from getting a lock?

Can someone give me an example of how to put a lock on a table or database and perhaps kill any users that are logged into that table, or database during an update program execution. That would really help me out!

I could really use a little education on this.

Thanks for taking the time.

bob

 

[NoCoolHandle]

Bob

Read locks will block Exclusive locks

Exclusive locks will block update and read locks

depending on the version of sql you are running
a single record opended for read will block all atempts to exclusive on the db if the read ins't being helped by an index..

you can bypass this behaviour in your readonly group by haveing all reports run with the [nolock] optimizer hint or via an ansi set statemnt (read uncommitted)

HTH

Rob

 

[NoCoolHandle]

Bob scratch the bit about the version 2k isn't subjected to this bad verison of lock all..
However everyting else is relevant.

 

[NoCoolHandle]

Bob... sorry need to read all your post


sp_who, sp_who2 and sp_lock will tell you all you need about locks

kill 34

will kill a process (spid umber 23) if you want it to..

However you still might want to look into the nolock option.

it will alow you to experience a "dirty read" and you need to be aware of that when using it.

By a dirty read, if a record is open for modificaton at the time of the select, you will see the value that would be commited if the update/insert were to commit.

If it errors and rolls back, the value read would be wrong.

HTH

Rob

 

[SQLSister]

The nolock hint can save tons of resources as well sas fixing the locking problem. We routinely use it on all access to our tables with over a million records for that reason.

Some other places you might find will create locking problems are the use of searches that use like '%sometext%' which prevents the use of indexes and slows down access considerably. We recently cured abunch of locking problems by changing a search to search for the exact match and only using the like statement if no records were returned.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[roswald]

NoCoolHandle and SQL Sister,
Thank you both for this invaluable information.
My dilemma is that we have an application for which we don't own the source code, otherwise I would lock these tables exclusively for the 20 minutes that the code runs and release them when finished. There are only 3 tables.

Is there a way to create a script that says give programA exclusive lock on table1,2,3...Just before programA kicks off at 5pm and another script that launches after programA finishes or perhaps the locks will disappear automatically once programA completes and is removed from memory?

As I said, we don't have the source and to have them revise the object would be very time consuming and costly.

Thanks again,

bob