| Author |
 Topic  |
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
 Posted - 07/11/2001 : 18:40:38
|
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 |
 |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 07/11/2001 : 19:00:58
|
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 |
|
|
|
inkntation
Yak Posting Veteran
54 Posts |
Posted - 07/12/2001 : 03:02:16
|
*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..?
|
|
|
|
Teroman
Posting Yak Master
United Kingdom
115 Posts |
Posted - 07/12/2001 : 08:48:01
|
just thought id say ive posted a solution to the problem dtong004 posted, no cursor used!
i hope its what you were after
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/12/2001 : 09:10:45
|
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
|
|
|
|
inkntation
Yak Posting Veteran
54 Posts |
Posted - 07/12/2001 : 10:09:57
|
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.
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/12/2001 : 10:14:43
|
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
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 07/12/2001 : 15:08:41
|
>> 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. |
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/12/2001 : 15:30:52
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 07/13/2001 : 10:53:30
|
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.
|
|
|
|
inkntation
Yak Posting Veteran
54 Posts |
Posted - 07/13/2001 : 11:25:08
|
quote:
Will this thread ever truly die?
Naw.. we gotta get over 300 read, and 3 pages of replies
|
|
|
|
btrimpop
Posting Yak Master
USA
214 Posts |
Posted - 07/13/2001 : 11:38:12
|
What the heck, what's one more reply!
There's a reason it's called a curse... uh ... cursor
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/13/2001 : 11:41:28
|
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
|
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 07/13/2001 : 11:42:18
|
Or Yaks!
===============================================
Creating tomorrow's legacy systems today.
One crisis at a time. |
|
|
|
inkntation
Yak Posting Veteran
54 Posts |
Posted - 07/13/2001 : 11:49:02
|
quote:
Or Yaks!
===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
YAKS? you talk about YAKS here too??!?
sheesh.
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 07/13/2001 : 11:49:40
|
Well I've just had several pints of fine english ale.
==========================================
Cursors are useful if you don't know sql. |
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/13/2001 : 11:58:26
|
Taking a cue from nr...
I hear australian beaches are just lovely this time of year.
Justin
|
|
|
|
inkntation
Yak Posting Veteran
54 Posts |
Posted - 07/13/2001 : 11:59:27
|
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
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/13/2001 : 12:05:59
|
Bass is pretty good but I looooove Killian's Red.
Justin
|
|
|
|
inkntation
Yak Posting Veteran
54 Posts |
Posted - 07/13/2001 : 12:10:06
|
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.
|
|
|
|
Topic |
|
how does row locking work
