MS SQL Server - Locking

Author

Locking

Jan Van der Eecken

2006-04-06, 8:23 pm

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

Andrew J. Kelly

2006-04-06, 8:23 pm

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
>

Jan Van der Eecken

2006-04-07, 3:24 am

Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.

Cheers,
Jan

"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.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...
>
>

Tibor Karaszi

2006-04-07, 3:24 am

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@shadh
awk.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...
>
>

Jan Van der Eecken

2006-04-07, 7:24 am

Thanks, Tibor

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.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@shadh
awk.com> wrote in message
> news:%23LP5XxdWGHA.2376@TK2MSFTNGP03.phx.gbl...
>

Kalen Delaney

2006-04-07, 11:23 am

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@hotmai
l.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@shadh
awk.com> wrote in message
> news:%23LP5XxdWGHA.2376@TK2MSFTNGP03.phx.gbl...
>

Jan Van der Eecken

2006-04-07, 8:23 pm

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_newsg
roups.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@hotmai
l.nomail.com> wrote
> in message news:%23dwfZahWGHA.4620@TK2MSFTNGP04.phx.gbl...
>
>

Sponsored Links