Database: Are there any vendors support column level locking?

Question

I'm studying about database mechanism and see that there are two mechanisms: table level locking and row level locking. I don't see column level locking and when I google, I see no document tell about this except this link: database locking. In this link:

A column level lock just means that some columns within a given row in a given table are locked. This form of locking is not commonly used because it requires a lot of resources to enable and release locks at this level. Also, there is very little support for column level locking in most database vendors.

So, which vendors support column level locking ? And can you tell me more detail, why column level locking requires a lot of resources than row level locking.

Thanks :)

Answer 1

A lock cannot, in and of itself, require anything. It's an abstract verb acting on an abstract noun. Why should locking a column cost any more than locking a byte, or a file, or a door? So I wouldn't put a lot of stock in your link.

The answer to your question lies in why locks exist -- what they protect -- and how DBMSs are engineered.

One of the primary jobs of a DBMS is to manage concurrency: to give each user, insofar as possible, the illusion that all the data belong to each one, all the time. Different parties are changing the database, and the DBMS ensures that those changes appear to all users as a transaction, meaning no one sees "partial changes", and no one's changes ever "step on" another's. You and I can both change the same thing, but not at the same time: the DBMS makes sure one of us goes first, and can later show who that was. The DBMS uses locks to protect the data while they're being changed, or to prevent them from being changed while they're being viewed.

Note that when we "want to change the same thing", the thing is a row (or rows). Rows represent the things out there in the real world, the things we're counting and tracking. Columns are attributes of those things.

Most DBMSs are organized internally around rows of data. The data are in memory pages and disk blocks, row-by-row. Locks in these systems protect row-oriented data structures in memory. To lock individual rows is expensive; there are a lot of rows. As an expedient, many systems lock sets of rows (pages) or whole tables. Fancier ones have elaborate "lock escalation" to keep the lock population under control.

There are some DBMSs organized around columns. That's a design choice; it makes inserts more expensive, because a row appears in several physical places (1/column), not neatly nestled between to other rows. But the tradeoff is that summarization of individual columns is cheaper in terms of I/O. It could be, in such systems, that there are "column locks", and there's no reason to think they'd be particularly expensive. Observe, however, that for insertion they'd affect concurrency in exactly the same way as a table lock: you can't insert a row into a table whose column is locked. (There are ways to deal with that too. DBMSs are complex, with reason.)

So the answer to your question is that most DBMSs don't have "columns" as internal structures that a lock could protect. Of those that do, a column-lock would be a specialty item, something to permits a certain degree of column-wise concurrency, at the expense of otherwise being basically a table-lock.