3

As far as i know, the lowest level locking is row-level

However i don't see the reason why it can not have column level locking

Assume that 2 queries simultaneously updates 2 different columns.

So why it can not happen? Why each update has to lock at least row level?

This behavior tells me that i should split table into smaller tables so less row level locking would happen

Am i incorrect?

8
  • I don't know a RDBMS that has column level locking.
    – vercelli
    Aug 18 '16 at 10:10
  • @vercelli i dont know either. but i wonder the reason behind it. Aug 18 '16 at 10:12
  • Too many resources to do that. Actually you should take a look at "In Memory OLTP for SQL Server (2014 and 2016). It has optimistic concurrency locks (I'm no expert there). msdn.microsoft.com/en-us/library/dn133186.aspx
    – vercelli
    Aug 18 '16 at 10:16
  • Or this: simple-talk.com/sql/learn-sql-server/…
    – vercelli
    Aug 18 '16 at 10:17
  • @vercelli i see. however it requires SQL server 2016 and i still afraid that data loss may happen :D though Memory OLTP looks very promising. i guess i can turn trivial tables into memory tables if i upgrade to SQL 2016. however i still wonder why not column-level lock Aug 18 '16 at 10:23
3

This feature could exist. It would consume resources and complicate the architecture. So it's a trade-off that had to be made.

For the same reason some databases go in the opposite direction: They lock on the page or table level. MongoDB even has a global write lock for the entire database! This surely makes some physical implementation concerns much easier.

SQL Server's locking model already is quite sophisticated and, in my experience, addresses practical scenarios very well. I'm not sure column level locking would make it a better product (even if the developer time were free for Microsoft).

2
  • so it is totally about decisional thing rather than technical possibility. if they wanted, they could have made it right? Aug 18 '16 at 11:02
  • Correct. An RDMS has tons of architectural choices. SQL Server seems to be one of the most pristine architectures that I see in the database space.
    – usr
    Aug 18 '16 at 11:07
0

The few times I thought column level locking was needed, I came to the realization later on that it was a bad schema design. The columns needing to be locked should have been placed in a separate table.

-3

Because even row-level locking is "fake" to some extent, no pun intended - the smallest thing you can really lock is a block on the underlying filesystem, which is the atomic unit of I/O, which may be anywhere from 512 bytes to 8k usually. So since you need to serialize at that level anyway, a column lock doesn't give you anything a row lock wouldn't concurrency and performance-wise.

14
  • A filesystem block can be larger than one row. It's a physical unit. A row lock does not lock any page necessarily (at least not using a high lock level).
    – usr
    Aug 18 '16 at 11:14
  • 1
    Two transactions could write to the same row and coexist. There will not be a physical update at the same time but at least the two trans do not conflict. Less blocking/deadlocking. It's the same advantage that row locking has (as opposed to table or page locking).
    – usr
    Aug 18 '16 at 12:17
  • 1
    @Gaius how about SSD? they dont have any heads Aug 18 '16 at 12:21
  • 1
    "a column lock doesn't give you anything a row lock wouldn't concurrency and performance-wise.". It could allow you to update the values in a column in a large table without blocking other queries that read different columns. All the physical stuff is irrelevant. Locks are logical. Latches take care of ensuring that only one process can write to a page in SQL Server, unlike locks these are not coupled to transaction lifetime. There is no prohibition on different processes acquiring row locks and writing to rows on the same page that all get written out at the same time.... Aug 18 '16 at 16:50
  • 2
    ...The physical hardware write is not synchronous with the logical operation. It happens later eg during a checkpoint. The only thing that needs to happen on the file system before the transaction commits is the write ahead logging. Aug 18 '16 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.