4

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?

Improve this question
8
  • I don't know a RDBMS that has column level locking.
    – vercelli
    Commented Aug 18, 2016 at 10:10
  • @vercelli i dont know either. but i wonder the reason behind it.
    – Furkan Gözükara
    Commented Aug 18, 2016 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
    Commented Aug 18, 2016 at 10:16
  • Or this: simple-talk.com/sql/learn-sql-server/…
    – vercelli
    Commented Aug 18, 2016 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
    – Furkan Gözükara
    Commented Aug 18, 2016 at 10:23

3 Answers 3

Reset to default
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).

Improve this answer
2
  • so it is totally about decisional thing rather than technical possibility. if they wanted, they could have made it right?
    – Furkan Gözükara
    Commented Aug 18, 2016 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
    Commented Aug 18, 2016 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.

Improve this answer
-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.

Improve this answer
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
    Commented Aug 18, 2016 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
    Commented Aug 18, 2016 at 12:17
  • 1
    @Gaius how about SSD? they dont have any heads
    – Furkan Gözükara
    Commented Aug 18, 2016 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....
    – Martin Smith
    Commented Aug 18, 2016 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.
    – Martin Smith
    Commented Aug 18, 2016 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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