Why SQL Server's ROWLOCK Hint Doesn't Always Just Lock Rows

In this video, I dive into a topic that might seem counterintuitive at first: the roll lock hint in SQL Server and why it's not as foolproof as some might think. I share a quick demo where we explore how even with a roll lock hint, you can still end up taking key locks, which isn't always what you want or need. The video also delves into the concept of lock escalation, showing that if your query hits certain thresholds, SQL Server will escalate from row-level to object-level locks, which can negate the benefits of using the roll lock hint. I hope this helps clarify some common misconceptions and provides useful insights for anyone working with SQL Server queries and locking strategies. CHAPTERS 00:00:00 - Introduction 00:01:29 - Delayed Start Mystery 00:03:02 - Roll Lock Hint Demo 00:04:02 - Lock Escalation Example 00:05:07 - Key Takeaways ━━━━━━━━━━━━━━━━━━━━━━━━━━ 📚 TRAINING & COURSES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Get AI-Ready With Erik https://training.erikdarling.com/get-ai-ready-with-erik?coupon=AIREADY SQL Server Performance Engineering Course https://training.erikdarling.com/sql-server-performance-engineering?coupon=ENGINEERING Learn T-SQL with Erik https://training.erikdarling.com/learn-t-sql-with-erik?coupon=ADVANCEDTSQL Everything Bundle: https://training.erikdarling.com/?coupon=SPRINGCLEANING ━━━━━━━━━━━━━━━━━━━━━━━━━━ 🛠️ CONSULTING & SERVICES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Need SQL Server performance help? https://training.erikdarling.com/sqlconsulting ━━━━━━━━━━━━━━━━━━━━━━━━━━ 💬 CONNECT ━━━━━━━━━━━━━━━━━━━━━━━━━━ Ask questions at Office Hours https://erikdarling.com/officehours/ Become a channel member https://www.youtube.com/@ErikDarlingData/join ━━━━━━━━━━━━━━━━━━━━━━━━━━