July 10, 2006 at 8:35 am
Have a report the developers run and each time this is run and another report is run it is blocked .The argument is they are read only?Why block other processes .
Currently running a trace to collect locking and deadlock info but not sure this has answers to why reads are able to block other processes.
Your input as usual is appreciated
July 10, 2006 at 1:40 pm
Well, it happens to me too! Same thing, running traces. Moreover, if a report is run in the security context of read-only user for the user DB his TempDB rights are write at least so TempDB is able to grow and you have to watch it. I have reporting / data extraction locking once in a while with both .NET and MySQL front ends with read-only users.
Regards,Yelena Varsha
July 11, 2006 at 6:30 am
Hi,
Your developers may only have readonly rights but if they run a standard select you will get shared locks on the tables.
Perhaps they should try and set the transaction isolation level to read-uncommitted.
Regards..Graeme
July 12, 2006 at 8:49 am
Are you saying when some one has read only access they will have shared blocking locks on tables they query?
Thanks
July 12, 2006 at 9:19 am
I am assuming you have set your developers to have the role of db_Datareader. All this means is that they cannot make any modifications to data or structure. When they select data, they are still bound by the locking rules of SQL Server.
A shared lock is created when a select statement is run. This means other connections can select data but cannot modify it.
I cannot comment on your reports as I have not seen them. If they are complicated you may be generating a lot of locks that are taking a while to be released.
Have you actually seen the process being blocked ?
Graeme
July 12, 2006 at 11:32 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply