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?

  • 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. – MonsterMMORPG 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 – MonsterMMORPG Aug 18 '16 at 10:23

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).

  • so it is totally about decisional thing rather than technical possibility. if they wanted, they could have made it right? – MonsterMMORPG 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

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.

  • 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
  • Sure but there is no such thing as simultaneous write to a block. You would need 2 disk heads for a start! So what advantage does individually locking 2 columns with a row give you? None, but a lot of extra complexity and wasted memory. – Gaius Aug 18 '16 at 11:18
  • 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 – MonsterMMORPG Aug 18 '16 at 12:21
  • 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. – Martin Smith 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.