SybaseBlog

I received the following question from (presumably) a student.

"I had a doubt can you kindly clarify me, and if you can send me details it will really help me to understand better. Thanks for your time.

Delete operation will be faster in
a) All page locking
b) Data page locking
c) Data row locking"

How would I find an answer to this question? First of all, I would consult Sybase documentation. It truly is excellent, but I've written before about other places to look if you're still stuck. I happen to have studied this in the past, and I know explanations on the different locking models are easy to find. Hint: All page locking locks data AND indexes, data only lock only lock the data.

In this case, you'll find your answer pretty easily. But in other cases, you'll probably answer every question with "It Depends." That is a very intelligent answer (assuming of course that you understand on what it depends).

But you're not done yet. Remember that documentation and the experts can be wrong. Also, things change, and the information you have been given may be out-of-date. You need to test your conclusions after you have reached them. You'll see in my blog that's how I learn: I create test cases.

In this case, it's easy. Create many identical tables that differ only in their locking schemes. Obviously you'll want lots of data in each of them. Then you can turn on timing statistics (here's how) and see how long it takes to delete. You're less likely to have made a mistake, and you'll learn a lot more by doing than by reading or listening. After you have done so, I'd be glad to share your information here.

Now you know the secret!