|
Home > Archive > MS SQL Server > October 2006 > shared lock (TABLOCK) behaving as exclusive (TABLOCKX)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
shared lock (TABLOCK) behaving as exclusive (TABLOCKX)
|
|
| Jeff Mowatt 2006-10-24, 6:33 pm |
| I have an application which has over many years run under SQL6.5, 7 and
2000 using the same locking mechanism without change.
It uses a shared table lock to allow multiple user access and for
administrator activity the same table lock is set to exclusive such
that it refuses non admin users attempting a shared lock.
It is installed on around 50 servers around the UK and behaved as
expected on all of them until a few days ago on the last installation
under SQL2000..
The app worked as expected with shared locks until a few days ago when
the TABLOCK parameter started to behave as if TABLOCKX had been used,
rendering the app single-user only.
The problem seems to coincide with an office move where servers are
being re-located and I'm wondering, if its possible that some system
setting unknown to me could have changed shared table locking
behaviour.?
| |
| Jeff Mowatt 2006-10-24, 6:33 pm |
| Forgot to say that the app leaves ISOLATION LEVEL to default (READ
COMMITTED) at the time of development, hopefully it still is.
| |
| JXStern 2006-10-24, 6:33 pm |
| On 2 Oct 2006 08:16:16 -0700, "Jeff Mowatt" <jeff.mowatt@gmail.com>
wrote:
>The problem seems to coincide with an office move where servers are
>being re-located and I'm wondering, if its possible that some system
>setting unknown to me could have changed shared table locking
>behaviour.?
You are SURE there are no other changes to the code?
Same SP?
IOW, I have no clue!
Josh
| |
| Jeff Mowatt 2006-10-24, 6:33 pm |
|
> You are SURE there are no other changes to the code?
>
> Same SP?
>
Absolutely no change in the best part of the last decade, Josh.
Service packs up to now have made no difference, these have all been
different businesses.
I believe this one to be a virtual server environment which is perhaps
the first time its been deployed this way.
| |
| Kalen Delaney 2006-10-24, 6:33 pm |
| How have you verified that it is behaving the same as TABLOCKX?
Have you checked sp_lock? Does sp_who actually show that users are blocked
on a process that is holding a shared lock?
If you look in sysprocesses, it will tell you what a waiting process is
actually waiting for.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Jeff Mowatt" <jeff.mowatt@gmail.com> wrote in message
news:1159802176.610695.22220@k70g2000cwa.googlegroups.com...
>I have an application which has over many years run under SQL6.5, 7 and
> 2000 using the same locking mechanism without change.
>
> It uses a shared table lock to allow multiple user access and for
> administrator activity the same table lock is set to exclusive such
> that it refuses non admin users attempting a shared lock.
>
> It is installed on around 50 servers around the UK and behaved as
> expected on all of them until a few days ago on the last installation
> under SQL2000..
>
> The app worked as expected with shared locks until a few days ago when
> the TABLOCK parameter started to behave as if TABLOCKX had been used,
> rendering the app single-user only.
>
> The problem seems to coincide with an office move where servers are
> being re-located and I'm wondering, if its possible that some system
> setting unknown to me could have changed shared table locking
> behaviour.?
>
| |
| Scott Morris 2006-10-24, 6:33 pm |
| "Jeff Mowatt" <jeff.mowatt@gmail.com> wrote in message
news:1159803990.104683.281150@b28g2000cwb.googlegroups.com...
> Forgot to say that the app leaves ISOLATION LEVEL to default (READ
> COMMITTED) at the time of development, hopefully it still is.
So you can't say with any certainty what the actual isolation level in use
is? Perhaps that should be your first step. Changing the isolation level
to something more restrictive would have this effect.
| |
| Jeff Mowatt 2006-10-24, 6:33 pm |
| Hi Kalen,
Only so far by remote stepwise observation of the effects based on more
than 10 years intimate knowledge of what it's doing and testing locally
without being able to make this happen. I know what status is returned
when a shared lock is attempted and fails. No user is ever blocked in
the sense of waiting, it just tells them that maintenance is being
performed, which in this case it isn't really.
Tomorrow, I'll re-supply with a log facility to write the sql commands
and compare the process locks on Enterprise Manager, I'm not familiar
with using sp_lock but presumably this will tell me much the same
thing? . .
Jeff
Kalen Delaney wrote:
> How have you verified that it is behaving the same as TABLOCKX?
> Have you checked sp_lock? Does sp_who actually show that users are blocked
> on a process that is holding a shared lock?
> If you look in sysprocesses, it will tell you what a waiting process is
> actually waiting for.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
| |
| Jeff Mowatt 2006-10-24, 6:33 pm |
| I'm not sure about this Scott, having experimented a lot with these
settings in the beginning. It's worked solidly for years, with
absolutely no indication of this behavour before anywhere else.
Scott Morris wrote:
> "Jeff Mowatt" <jeff.mowatt@gmail.com> wrote in message
> news:1159803990.104683.281150@b28g2000cwb.googlegroups.com...
>
> So you can't say with any certainty what the actual isolation level in use
> is? Perhaps that should be your first step. Changing the isolation level
> to something more restrictive would have this effect.
| |
| Kalen Delaney 2006-10-24, 6:33 pm |
| Maybe it's something in your app. SQL Server normally doesn't 'return a
status' when a lock is attempted and fails. sp_lock reports similar
information to the process locks, but is much less resource intensive to
run. Plus it returns text data that can be posted here, along with the
related rows from sysprocesses.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Jeff Mowatt" <jeff.mowatt@gmail.com> wrote in message
news:1159818893.128506.147940@i42g2000cwa.googlegroups.com...
> Hi Kalen,
>
> Only so far by remote stepwise observation of the effects based on more
> than 10 years intimate knowledge of what it's doing and testing locally
> without being able to make this happen. I know what status is returned
> when a shared lock is attempted and fails. No user is ever blocked in
> the sense of waiting, it just tells them that maintenance is being
> performed, which in this case it isn't really.
>
> Tomorrow, I'll re-supply with a log facility to write the sql commands
> and compare the process locks on Enterprise Manager, I'm not familiar
> with using sp_lock but presumably this will tell me much the same
> thing? . .
>
> Jeff
>
> Kalen Delaney wrote:
>
>
| |
| Jeff Mowatt 2006-10-24, 6:33 pm |
| Ah now that raises another possibility. Maybe ODBC , which is what I'm
using and something I hadn't yet thought about, could be a factor in
this. It normally returns me a nonzero status or an Errorflag in an
Errinfo object when the lock attempt fails..
Kalen Delaney wrote:
[color=darkred]
> Maybe it's something in your app. SQL Server normally doesn't 'return a
> status' when a lock is attempted and fails. sp_lock reports similar
> information to the process locks, but is much less resource intensive to
> run. Plus it returns text data that can be posted here, along with the
> related rows from sysprocesses.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
>
> "Jeff Mowatt" <jeff.mowatt@gmail.com> wrote in message
> news:1159818893.128506.147940@i42g2000cwa.googlegroups.com...
|
|
|
|
|