Details of impact of different locking schemes in databases?

Question

I'm testing different locking schemes for a big table, more than 4 million rows and expected to grow up to 10 million.

My experience with SyBase:
ALLPAGES, extremely slow BCP (in) and update with a field that belongs to the Primary Key.
DATAROWS, fast BCP, fast update for fields in the PK, fastest select.
DATAPAGES, fastest BCP and update, and faster select than ALLPAGES but not as fast as DATAROWS.

I'm not an expert, but it seems to me that DATAROWS blocks more than DATAPAGES, and I haven't seen any downside with DATAPAGES.

The only point in favor for DATAROWS that I could find was a bit faster performance in selects that use primary key, but DATAPAGES is considerably faster at updates.

Any experiences in practice?
How does this locking schemes compare?
Pros/Cons of each one?

I tried to read about the theory behind it, but it's not very clear to me.

Answer 1

Locking is a concurrency control mechanism. It ensures the consistency of data within and across transactions. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time, so you should not choose locking schemes according to e.g. BCP speed.

For tables that use allpages locking or datapages locking, either page locks or table locks

For tables that use datarows locking, either row locks or table locks

But allpages-locked tables protect against phantoms (rows that can appear or disappear from a results set) by holding locks on the index pages for the serializable read transaction

In general you should use datarows or datapages locking for tables with lock contention due to updates and selects.

Read more about locking and performance here