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
Next Page
Author Previous Topic Topic Next Topic
Page: of 5

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/10/2001 :  16:00:02  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
can anyone point me to documentation on how exactly row level locking works in sql 7? im STILL having to make my case against an ignorant dba that doesnt understand it at all (and yes, he is being PAID to know this stuff) ive proven it in production, ive shown him lock managment documentation from msdn, ive sent him link after link, and he still doesnt think it was meant to be used in an update statement. whats worse is he is pushing now and has been for a while to use cursors for multiple row updates. and since hes the dba and no one else here other than myself has had any degree of experience with developing for sql server.. im the one who has to make the case.
so any help you guys could provide would be greatly appreciated.. links would be good (preferably) or just a detailed explanation..

thanks



rpaller
Posting Yak Master

USA
137 Posts

Posted - 07/10/2001 :  16:38:35  Show Profile  Reply with Quote
Personally, I think Books Online does a decent job of explaining how locking works. If your DBA has read that and still does not understand it you may have to resort to drawing pictures on a white board. Have him check out Microsoft SQL Server 2000 DBA Survival Guide
(http://www.amazon.com/exec/obidos/ASIN/067232007X/qid=994797083/sr=1-20/ref=sc_b_20/002-7571460-5801606). This should be a good read for him.

Regards,
Rob

Go to Top of Page

robvolk
Most Valuable Yak

USA
15665 Posts

Posted - 07/10/2001 :  17:40:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
The other option is shooting him :)

While you're at it, ask if you can get the same compensation package that schoolteachers get, since that seems to be your job right now.

Rob is totally right about BOL; I think that if your DBA doesn't get it after reading it there, he's hopeless. Perhaps a gander at Inside SQL Server would help too. If you haven't gotten this book yet you should.

Have him prove that cursors work better. Why should you do all the work? If you already ran them side-by-side and ROWLOCK works faster, what's the problem?

Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/10/2001 :  18:10:32  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
<rant>

Well, I didnt run time comparisons to see which was faster, my the proof in MY pudding is that it WORKS. On top of that, it works the first time, every time. Before, the lock contention on the tables was so bad, it COULDNT run. It errored out every time.. as a matter of fact, it was so bad, that the man who ran the process had to wait till around 10pm every night when things slowed down. You should have seen a face that nearly had tears of joy at seeing it run the first time around, and has every day since I implemented rowlock.
The performance related problems on our databases are so bad, its ridiculous. Seriously. response time is crap, every time you open Enterprise Manager and click the + beside the server names to expand and see the db stuff, it times out the first time, every time.. and 90% of the time, times out when you open query analyzer..
we have some tables that are 45 million + rows .. that are still in one table and not horizontally partitioned. As a matter of fact, I brought that point up, and people gave me puzzled looks, and said "What good would doing THAT get you?". Im not even sure how often the indexes and such are maintained. It wouldnt suprise me if the maintainence schedule is somewhat lax.
On top of that, like in my initial post, he's INSISTING, that every time we do multiple row updates to any major table, we MUST use cursors. As a matter of fact, every single sp I've seen since I started, contains at least 1 cursor (thats right, multiple cursors in 1 sp).
In addition, I am going to be somewhat involved (not sure to what degree) in a short project to re-write 3 sp's that currently are written correctly (minus any table hints) and have never errored because of lock contention, the dba has been pushing to have these redone into cursor based sp's. While I dont argue that the sp's need rewritten (a lot of redundancy between the 3) I loathe to use cursors.
I hate to be one to step on toes, but its unbelievably frustrating to work like this. Everything my experience has taught me, in addition to all the knowledge thats freely available online, is screaming against the entire enviornment, right down to the use of named pipes over tcp/ip to connect to sql server.
*sigh*
</rant>

Go to Top of Page

robvolk
Most Valuable Yak

USA
15665 Posts

Posted - 07/10/2001 :  18:32:40  Show Profile  Visit robvolk's Homepage  Reply with Quote
Unless this DBA is the tallest hog in the trough, perhaps you should have someone higher up read these posts, as well as the links you provided. Or even have ultra-schmuck himself read it. The only thing I can think of is that he doesn't want to be shown up by some "newbie", fearing that someone will finally realize he's incompetent.

Whatever you do, don't give in and do the re-writes with cursors. Let him scream, and tell him if he wants them done as cursors, let him do it himself. When they die, no one can blame you. Even if you aren't supposed to do the rewrite, do it secretly, and try testing it, just to have it done should it be needed.

Lastly, don't let it get you down, but by the same token, if it continues on like this, think about going somewhere else. With luck, you'll end up consulting and will be called in (at a much higher rate) to fix their problems later

Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/11/2001 :  07:44:04  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
quote:

Unless this DBA is the tallest hog in the trough, perhaps you should have someone higher up read these posts, as well as the links you provided. Or even have ultra-schmuck himself read it. The only thing I can think of is that he doesn't want to be shown up by some "newbie", fearing that someone will finally realize he's incompetent.

Whatever you do, don't give in and do the re-writes with cursors. Let him scream, and tell him if he wants them done as cursors, let him do it himself. When they die, no one can blame you. Even if you aren't supposed to do the rewrite, do it secretly, and try testing it, just to have it done should it be needed.

Lastly, don't let it get you down, but by the same token, if it continues on like this, think about going somewhere else. With luck, you'll end up consulting and will be called in (at a much higher rate) to fix their problems later





Heheh, its not gonna get me down. Im just frustrated because I feel that I indeed HAVE proven my point well enough and provided enough documentation. I think the functionality speaks for itself.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/11/2001 :  08:52:46  Show Profile  Visit nr's Homepage  Reply with Quote
Love it, sounds like my type of company. You sound like you need to develop your skill level in sarcasm, irony, arrogance for meetings - all things needed by a good dba.

Your dba is probably correct for v6.5 about locking and doesn't believe v7 is any different - and for a reasonably designed system it isn't much (but you don't have that do you).

Unfortunately some people take the approach 'This is supposed to be my job so I'm not going to listen to you'.
I take the approach
'This is my job and I'm happy to prove I know better than you - and if I don't I'll just get drunk again'.

About cursors for the updates - well saves having to know about any relational databases. Perhaps you could post your it directors phone number - sounds ripe for some easy highly paid consultancy.

p.s. I have only ever written 1 cursor (+ 1 I posted on this site as a joke).

>> The performance related problems on our databases are so bad

Well there's a surprise - probably not enough cursors.

There used to be a magazine called Freelance Informer with a humourous series about the life of a contractor - sounds like you could write a similar series for this site.

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

Edited by - nr on 07/11/2001 08:57:31
Go to Top of Page

inkntation
Yak Posting Veteran

54 Posts

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

Love it, sounds like my type of company. You sound like you need to develop your skill level in sarcasm, irony, arrogance for meetings - all things needed by a good dba.




I'm quite at sarcasm, but with me being new here already, I think its a bit premature to pull the techie prima donna stuff out :-) heheheh
although i have thought for a while to pursue dba certification. ive already studied quite a bit for it, but i think being in a difficult environment will toughen me up some LOL



Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/11/2001 :  10:51:22  Show Profile  Visit nr's Homepage  Reply with Quote
Never really been that impressed by certification (never had time or inclination to take any of the exams).
Your dba probably has lots of paper, little experience.
Or is that being harsh?

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

inkntation
Yak Posting Veteran

54 Posts

Posted - 07/11/2001 :  11:05:30  Show Profile  Send inkntation a Yahoo! Message  Reply with Quote
quote:

Never really been that impressed by certification (never had time or inclination to take any of the exams).
Your dba probably has lots of paper, little experience.
Or is that being harsh?

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


I was thinking certification to gain a bit more credibility to people who dont know. I know certs mean about as much as a bag of crap to other techies, I'm the same way. The dba however, I'm not sure if hes got any or not. Apparently at one point there was 2, and the current was the secondary.. or assistant, whatever. The primary was a contractor (they both are/were) and more expensive, the other one was kept around because he (presumably) was cheaper, and familiar with the enviornment.
I guess initially the servers were installed and configured by someone who "shouldnt be allowed near computers" and the current bundle of joy was given to the current dba when assumed full responsibility. So I can understand certain aspects of the situation, but I dont understand others *coughcursorscough* and why there are no steps being taken to enhance the current architecture, and resolve the performance issues. Of course, seeing as how they only just moved to SQL Server 6.5 1.5 years ago, then to SQL 7 maybe 8 months or so ago, from Paradox, they dont notice the poor performance as much.. Hell, to them its still an upgrade.
However, in my opinion timeouts as frequent as ours are unacceptable. Sometimes it sucks knowing how to fix something when you arent allowed to fix it.


Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 07/11/2001 :  14:44:46  Show Profile  Visit dtong004's Homepage  Reply with 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".

Somebooks call these DBMS internal cursor. And they runs maybe hundreds times faster than the cursor you wrote.

What I am saying is: be open to other terms. They might not wrong and you might not right.

Ps: Certificate is good to have. I would think person who certified have gone through some sort of training. With a Certification is definitely better than without. However, a certificate cannot gurantee a good DBA. Pretty much like a CPA does not gurantee a good accountant.

Daniel










Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/11/2001 :  15:03:30  Show Profile  Reply with Quote
quote:

What I am saying is: be open to other terms. They might not wrong and you might not right.



There is a pretty big difference between interchangeable terms (data set / record set)
and confusing two different concepts and writing it off as dissimiliar terminology. That's the kind of thing that can start problems like the one above. If the dba thinks that using a cursor is the same as set based solutions because he is under the assumption that SQL Server is using a cursor behind the scenes then you have a pretty big problem.

Justin

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 07/11/2001 :  15:24:58  Show Profile  Visit dtong004's Homepage  Reply with Quote
Show me the difference. Besides the RDBM internal operation such as read-ahead or index usuage.

Daniel

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/11/2001 :  16:45:42  Show Profile  Reply with Quote
quote:

Show me the difference.



One table with 10 rows ordered by column x.

You are looking at the data on row 8.

You know decide you want to look at the data on row 5.

The cursor can jump back three rows. A set based query has to start from the beggining of the set and find the fifth row, rather than jumping from what it knew to be the eighth.

A cursor (regardless of whether you create it or the system decides to) is a CONSTRUCT. A relational database system is a METHEDOLOGY for oganizing and manipulating data.

What you are saying is that dying from natural causes is the same as dying from a gunshot wound just because they both happen to stop your heart beat.

Justin

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 07/11/2001 :  17:27:33  Show Profile  Visit dtong004's Homepage  Reply with Quote
You did not make the case. Justin.

Here is an example. A table without index (we just set index aside first). Now. You want to update the table, here is your query:

update xxx
set xxxx
where ......

Your where clause determine 3 rows, let's say. There 3 rows located in 3 different pages. Tell me what SQL server will do.

SQL server gets the set. Do you think it will update 3 rows by sets? No. It will updates row by row, one record by one record. In otherwords, the hard disk need to spin thhree times.

SQL server probably will use page lock at this scenario.

Let us look using curosor.

You define cursor as
"select xxx from xxxx where xxxx"
this gives you three row sets.
next in your cursor operation, you do row by row updates (This makes you think of row records).

What SQL will do. Not surprise, SQL will do three row updates, one by one, row by row. The disk need to spin three times also.

In this scenaro, SQL server may take row leverl locking.

Tell me the difference.!

What I am trying to make is: "cursor" is not a monster. SQL server internally still doing the work as an cursor.

Then, why users' "cursor" slower than Systems internal couterpart(or you say, sets) ? As the internal cursor can take advantage RDBMS advantages such as: read-ahead. The system knows what is going to do with row2 when it process row1. While users' "cursor" won't be able to let system know that.

Well, in Oralce's complicated curosor procedure, you might be able to hint RDBMS.

Just recap: Cursor is not a monster. When the business requres a cursor based procedure. Go ahead. Just be careful, do NOT make the recordset too big. You barely can the difference. And cursor based procedure DOES provide flexiblity and row-level locking.

Daniel


Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/11/2001 :  17:38:04  Show Profile  Reply with Quote
At the most granular level of data modification the one piece of data will be changed at a time, this I agree on. But by your statement a cursor based solution should require the exact same resources and time as a set based solution. By that statement there would NEVER be any difference in the performance of the two solutions. 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.

Justin



Edited by - justinbigelow on 07/11/2001 17:41:13
Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 07/11/2001 :  17:44:59  Show Profile  Visit dtong004's Homepage  Reply with Quote
I did not make the statement that cursor will be same fast as "sets" operation. However, cursor can do some work that "sets query' cannot do. Here is an example. So far, I have not get anyone back without use query.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6787&FORUM_ID=5&CAT_ID=3&Topic_Title=Merge+TWO%2C++Problem+is+not+solved&Forum_Title=Developer

I am gld if you come up with a solution.

Daniel



Go to Top of Page

robvolk
Most Valuable Yak

USA
15665 Posts

Posted - 07/11/2001 :  17:47:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:

A cursor (regardless of whether you create it or the system decides to) is a CONSTRUCT. A relational database system is a METHEDOLOGY for oganizing and manipulating data.

What you are saying is that dying from natural causes is the same as dying from a gunshot wound just because they both happen to stop your heart beat.



Bravo! Very nicely said!

May I throw more fuel on this particular fire? Consider this:

You have a haystack, there are needles in it. You only want the needles.

You can use tweezers to examine each straw in the haystack one at a time, and keep only the needles. Or you can use a magnet that will pull the needles out all together in one operation.

Cursors are tweezers. SQL is a magnet. Which would you prefer to use?

More to the point, SQL (the magnet) would affect ONLY the needles and ignore the straws completely, as if they never existed. The cursor (tweezers) still sees the straws, even if it ignores them.

Any other thoughts?

Daniel, pick up Inside SQL Server by Microsoft Press and read it. There are wonders galore on read-ahead (SQL Server uses this) and transaction log writing (the log AND the data are WRITTEN ahead as well). Not to mention the cache and buffers, and that it keeps locks in memory and not on disk to improve performance.

It sounds to me like you believe SQL Server just skips around a disk looking for data almost at random, and it simply DOES NOT DO THAT.

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 07/11/2001 :  18:01:58  Show Profile  Visit dtong004's Homepage  Reply with Quote
robvolk.

Obvisously, the Magnet and tweezer do NOT apply the "sets" and "cursor"

It is more like, production line vs indvidual operation. Too a small job, there is no singnificant difference. However, "cursor" does give you flexibility.

The rest of your message, I have agreed on. (my previous answer to Justin).

Daniel


quote:

quote:

A cursor (regardless of whether you create it or the system decides to) is a CONSTRUCT. A relational database system is a METHEDOLOGY for oganizing and manipulating data.

What you are saying is that dying from natural causes is the same as dying from a gunshot wound just because they both happen to stop your heart beat.



Bravo! Very nicely said!

May I throw more fuel on this particular fire? Consider this:

You have a haystack, there are needles in it. You only want the needles.

You can use tweezers to examine each straw in the haystack one at a time, and keep only the needles. Or you can use a magnet that will pull the needles out all together in one operation.

Cursors are tweezers. SQL is a magnet. Which would you prefer to use?

More to the point, SQL (the magnet) would affect ONLY the needles and ignore the straws completely, as if they never existed. The cursor (tweezers) still sees the straws, even if it ignores them.

Any other thoughts?

Daniel, pick up Inside SQL Server by Microsoft Press and read it. There are wonders galore on read-ahead (SQL Server uses this) and transaction log writing (the log AND the data are WRITTEN ahead as well). Not to mention the cache and buffers, and that it keeps locks in memory and not on disk to improve performance.

It sounds to me like you believe SQL Server just skips around a disk looking for data almost at random, and it simply DOES NOT DO THAT.





Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/11/2001 :  18:19:15  Show Profile  Reply with Quote
quote:

Obvisously, the Magnet and tweezer do NOT apply the "sets" and "cursor"

It is more like, production line vs indvidual operation. Too a small job, there is no singnificant difference. However, "cursor" does give you flexibility.



Alright let's stay with the analogies shall we. Your likening of set manipulation is wrong. Lets think about it as a flyer.

You're flyer has 30 lines of text on it.

You can copy each line one at a time or if you can use a stamp and press all 30 down at one time. That is set manipulation.

Justin




Edited by - JustinBigelow on 07/11/2001 18:47:09
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/11/2001 :  18:27:04  Show Profile  Reply with Quote
quote:

I did not make the statement that cursor will be same fast as "sets" operation. However, cursor can do some work that "sets query' cannot do. Here is an example. So far, I have not get anyone back without use query.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6787&FORUM_ID=5&CAT_ID=3&Topic_Title=Merge+TWO%2C++Problem+is+not+solved&Forum_Title=Developer



I never started making statements about what can and cannot with cursors or set statements. This was a thread concerning performance differences.

Justin



Edited by - JustinBigelow on 07/11/2001 18:30:25
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
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.19 seconds. Powered By: Snitz Forums 2000