SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 how does row locking work
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 07/11/2001 :  18:40:38  Show Profile  Visit Merkin's Homepage  Reply with Quote
quote:

Ask the DBA what is the meaning of his defination of "cursor". I have met some devleoper from Oracle side, they define following query is a cursor:

select * from table A where ......

others like:
update xxx where xxxx.

Frankly, I agree with them these kind of statment is a cursor. They just don't have a syntax like "declare cursor".




I am sorry Daniel but I think you are just plain wrong.
A Select statement returns a result set. A Cursor opens a result set and processes it row by row.

They are two separate things.

quote:

Ps: Certificate is good to have. I would think person who certified have gone through some sort of training.



Not necessarily. I have worked with people who became certified just be reading the books but not having any real experience. When it came to anything even slightly out of the scope of the books, they were lost.






Damian

Edited by - merkin on 07/11/2001 18:41:35
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 07/11/2001 :  19:00:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Not necessarily. I have worked with people who became certified just be reading the books but not having any real experience. When it came to anything even slightly out of the scope of the books, they were lost.



I have seen that as well. I have found many of the MS certs make people think like Microsoft, program like Microsoft, and it renders them helpless when confronted with a non-Microsoft problem, something not covered in the test prep. It's like they still drive like they did on their road test, hand signals and all.

Anybody remember what this thread was about? Oh yeah, rowlocks!

Dude, just use ROWLOCK! No cursors!

Edited by - robvolk on 07/11/2001 19:01:36
Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/12/2001 :  03:02:16  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
*yawn*
*comes home from the bar*
*fires up computer*
*KABAM**KABAM**KABAM**KABAM**KABAM**KABAM**KABAM**KABAM**KABAM**KABAM**KABAM**KABAM**KABAM* You have 13 New messages!
wha wha whhaaaat? *in kyles moms voice from southpark*
this is great heheheh
im glad i provoked some good discussion.
tawk amungst ya selves, im verclempt!
heres ya tawpic,
a cursor... its neither a cursor nor a set.
*clutches throat*
DISCUSS!
(in case you didnt get it.. that was linda richmond from coffee talk on saturday night live)
one other thing I would point out daniel, all your references to cursors that arent cursors, well, other than being hideously inaccurate.. remember, we're talking Microsoft SQL Server 7 here.. not any version of Oracle.
and just to clarify, the cursors im talking about ARE cursors (at least im assuming they are with the syntax DECLARE CURSOR SuckMyServersResources_Hard AS
etc, etc, etc, etc

)
anywho..
im tired. im really not thinking real clear right now but i did want to thank you guys for the tantalizing discussion and point everyone back to the real task at hand..
can anyone explain to me the indepth technical details (and maybe point out documentation) on exactly how rowlock works?

heres a bit im a tad curious about.. does it a.) go through and decide what rows need updated, then lock all of them at once, update them one by one and release them as theyre updated, or does it release once all the rows are updated? or does it b.) decide what rows need updated, then go to row 1, lock, update, release, move on.. etc..?



Go to Top of Page

Teroman
Posting Yak Master

United Kingdom
115 Posts

Posted - 07/12/2001 :  08:48:01  Show Profile  Send Teroman an AOL message  Reply with Quote
just thought id say ive posted a solution to the problem dtong004 posted, no cursor used!

i hope its what you were after

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/12/2001 :  09:10:45  Show Profile  Reply with Quote
quote:

heres a bit im a tad curious about.. does it a.) go through and decide what rows need updated, then lock all of them at once, update them one by one and release them as theyre updated, or does it release once all the rows are updated? or does it b.) decide what rows need updated, then go to row 1, lock, update, release, move on.. etc..?



Pretty sure that it's only a single record at a time regardless of the number of records to be updated. The reason being that you have specified the rowlock option in your query, if you left it up to sql server it would think about moving up to a page lock or table lock depending on the size of your update. If you are only updating a single row obviously hint or no hint the server would go for a row lock.

Justin

Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/12/2001 :  10:09:57  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
I had read somewhere though that the default lock method for sql server was table lock .. and all the updates try for table locks, unless told otherwise, because in terms of speed, that is the most resource friendly way of doing it. Though the negotiation in the lock escalations could put it back up to a table lock.

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/12/2001 :  10:14:43  Show Profile  Reply with Quote
From BOL
----------
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold.

For example, when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher level intent locks on the pages and table, or index, that contain those rows
-----------------

I think the key is to differentiate actual locks and intent locks.

Justin


Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/12/2001 :  15:08:41  Show Profile  Visit nr's Homepage  Reply with Quote
>> If you can show me a SINGLE cursor based query that I can't make perform either faster OR slower with a set based solution then I'll admit that the two solutions are totally interchangeable in terms of resource requirements and time.

Would be nice but is a dangerous stance to take.
Although cursors usually do give poor performance my main problem with them is there use usually shows a lack of understanding of sql and database structure.

sql server talks to the outside world via cursors - i.e. it generates a cursor then delivers it so and time you do a select ... you are using a cursor of some type - that's why you can talk about the fisrt record in a result set.


==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/12/2001 :  15:30:52  Show Profile  Reply with Quote
I may have successfully confused myself or blurred the lines of distinction between cursors as a concept and cursors as a construct.

I've agreed with dtong that in concept data is manipulated in a sequential manner. When a result set is generated one row is added, then the next, then the next, etc... until the result set is complete.

However, and this maybe due to my own interpretations of the posts, some seem to think that cursors (actual constructs residing somewhere in memory) are being generated behind the scenes to manage data. Much like a connection object being implicitly created when a rs is populated without an explicit conn object. So that means an internal cursor calls the data and then feeds it to the result set or an internal cursor feed data to an external cusor (the one directly controlled by the developer). I'm not saying that others believe a cursor like declarable cursors are being generated internally but they believe something very similar to that is, a system level cursor if you will. And I just haven't seen anything definitive to validate that theory.

Need to add this, I made a statement but no point... If people are under the assumption I laid out above then they use that as justification to design a system poorly or look for the easiest solution (sometimes involving cursors) rather than the best solution.

Justin



Edited by - justinbigelow on 07/12/2001 15:58:44
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 07/13/2001 :  10:53:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
Will this thread ever truly die?

I did find this really good article on www.sqlservercentral.com :

http://www.sqlservercentral.com/columnists/achigrik/lockinginsqlserver65.asp

It's specfic to SQL 6.5, and it doesn't cover row locking much (the implementation is different in 6.5), but it very nicely details not only the locking mechanisms but also the transaction isolation level and lock escalation.

www.swynk.com may also have more details on row locking (Alexander Chigrin writes articles for both sites), and maybe you can contact him directly for more info.

Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/13/2001 :  11:25:08  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
quote:

Will this thread ever truly die?





Naw.. we gotta get over 300 read, and 3 pages of replies

Go to Top of Page

btrimpop
Posting Yak Master

USA
214 Posts

Posted - 07/13/2001 :  11:38:12  Show Profile  Reply with Quote
What the heck, what's one more reply!

There's a reason it's called a curse... uh ... cursor

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/13/2001 :  11:41:28  Show Profile  Reply with Quote
quote:

Will this thread ever truly die?



I think the most amazing thing is that we have for the most part stayed on topic. Its rare to see a thread over one page that doesnt concern beer and/or australian geography.

Justin

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/13/2001 :  11:42:18  Show Profile  Visit graz's Homepage  Reply with Quote
Or Yaks!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/13/2001 :  11:49:02  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
quote:

Or Yaks!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.


YAKS? you talk about YAKS here too??!?
sheesh.


Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/13/2001 :  11:49:40  Show Profile  Visit nr's Homepage  Reply with Quote
Well I've just had several pints of fine english ale.

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/13/2001 :  11:58:26  Show Profile  Reply with Quote
Taking a cue from nr...

I hear australian beaches are just lovely this time of year.

Justin

Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/13/2001 :  11:59:27  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
quote:

Well I've just had several pints of fine english ale.

==========================================
Cursors are useful if you don't know sql.



British beer is my favorite. I like Bass personally. Although the stuff they sell here probably isnt anywhere near as good as it is there

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/13/2001 :  12:05:59  Show Profile  Reply with Quote
Bass is pretty good but I looooove Killian's Red.

Justin

Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/13/2001 :  12:10:06  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
quote:

Bass is pretty good but I looooove Killian's Red.

Justin





Killians? eh.. not really, while its dark it tastes like Bud Light.. *HEAVE* I can NOT drink that crap (bud light).. or anyother bud product actually.. it leaves this horrible taste in my mouth and a waxy feel on my tongue.


Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000