Locking

Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der EeckenI don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
--
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking levels were across different
>> versions of SQL, i.e. row vs. page vs. table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the other? Guess versions 4.2 and
>> 6.x were page-locking no matter what if I remember correctly, but want to make sure before I tell
>> my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||They called it INSERT-ROW locks in SQL 6.5, but it was not row level
locking. It was just a new type of page lock that allowed multiple inserts
on the same page.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||Thanks for the answers all of you ppl! Got the answer to my colleague
correct after all. But I really appreciate your help, and I really feel
honoured since I only got replies from MVP's..Obviously my question was kind
of obscure.
Nite,
Jan Van der Eecken
Cape Town
South Africa
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23spoQjlWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> They called it INSERT-ROW locks in SQL 6.5, but it was not row level
> locking. It was just a new type of page lock that allowed multiple inserts
> on the same page.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
>> 6.5 added some possibility to get some type of row locks for INSERTs.
>> Sorry to be vague, but it was a long time ago. As I recall, you had to
>> satisfy a number of conditions (probably what indexes you had) in order
>> to get some concurrency on the page for INSERT operations.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the
>> other? Guess versions 4.2 and 6.x were page-locking no matter what if I
>> remember correctly, but want to make sure before I tell my colleagues
>> rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>>
>

Locking

Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der Eecken
I don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>
|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>

Lock identifying through SP code

We would have come across locks on rows, tables etc. How do we handle it
through code (SP)!!!

A job is executed at 12 pm midnight on a table. This table exists on an
24X7 accessed (users will be access the table anytime in the day/night)
database. We need to execute this job if this table is not locked at 12 pm.
Can we have the solution for the listed are two scenarios:

1. You have a normal user and do not have system privilege.
How would you design the database(Table)? Is it possible?
2. You have all the privileges on the system tables.
What would be the sql script to help us identify the lock? How will
it be used in your code? Or is there exception handling !!!You can see if a lock exists in syslockinfo table:

if exists (select 1 from master.dbo.syslockinfo (nolock) where rsc_objid=object_id('dbo.yourtable') print 'Someone is using the table'
else begin
print 'Table is not in use'
begin tran
select top 1 [lockit]=1 from dbo.yourtable
--do your thing
if @.@.error != 0 rollback tran
else commit tran
end

LoadSnapshot: Item with session: ...

I have a application that uses the Report Viewer control. I ran across a
strange error executing a report.
What does this error message mean and how do you fix it?
w3wp!session!5!04/13/2007-22:38:55:: i INFO: LoadSnapshot: Item with
session: yzyevt45bsjnhi55g1tpso45, reportPath: , userName: MY_NET_1\DB not
found in the database
w3wp!library!5!04/13/2007-22:38:55:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ExecutionNotFoundException:
Execution 'yzyevt45bsjnhi55g1tpso45' cannot be found, ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ExecutionNotFoundException:
Execution 'yzyevt45bsjnhi55g1tpso45' cannot be foundHello Parker,
I would like to know this issue more detailed.
1. Did you use the ReportingViewer to connect to the Server report?
2. Have you applied the latest services pack 2 on the report server?
3. If you restart the IIS in the report server, did this issue still
occured?
4. What's the Application pool identity you use of the Application pool for
Reporting Services? If you change it to the Domain admin, did this issue
still occur?
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sorry I haven't had a chance to get back sooner.
1. Yes I did use the ReportViewer to connect to the Server report.
2. No, SP2 has not been applied. We are scheduling this for the weekend.
3. This only happened once so far. But there is not normal production use
yet.
4. We will change pool identity Domain admin this weekend.
So for now, we will make the changes and wait to see if it occurs again.
Thanks for the help.
"Wei Lu [MSFT]" wrote:
> Hello Parker,
> I would like to know this issue more detailed.
> 1. Did you use the ReportingViewer to connect to the Server report?
> 2. Have you applied the latest services pack 2 on the report server?
> 3. If you restart the IIS in the report server, did this issue still
> occured?
> 4. What's the Application pool identity you use of the Application pool for
> Reporting Services? If you change it to the Domain admin, did this issue
> still occur?
> Please let me know the result and so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
I am seeing this same behavior in a completely different environment.
1) application that uses the Report Viewer connect to the Server report.
2) SP2 is applied
3) I restarted IIS on the RS server, but it did not correct the issue. I
also restarted the RS server, but the issue continues.
4) I don't currently have the capability to adjust the Application pool
identity, but am trying to get that changed now.
Do you have any further information on what might be causing this error?
thanks.
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Wei Lu --
I too am experiencing the Execution 'xxx' cannot be found error. I am
using ReportViewer, with SP2 is installed. This is a local RS install
using the Network account on XP SP2.
The problem is very intermittent -- occurred 3 times today. Twice when
setting report parameters and clicking 'View Report', another when
clicking the double-arrows to show/hide the parameter area. There was
no length of inactivity (some users reported problems after 10 minutes
of inactivity -- I think this is a different issue).
Any help would be appreciated!
Thanks,
Chris