Posted by 
In this post I’m going to talk about locks on SQL Server. Locks are necessary, they are used in all operations in the database. Don’t get confused about blocking, locking and blocking are totally different.
When we talk about lock, doing something in the database, like an update and select though will cause a type of lock. The select stantement has a lock operation called shared lock. This means you can share reads with someone else and that may not cause blocks.
SQL Server has different kinds of lock modes, such as (S) Shared, (U) Update, (X) Exclusive, (I) Intent (Sch) Schema, Bulk Update and Key-Range.
- (S) Shared lock is used in read operations.
- (U) Update to avoid potential deadlock problem.
- (X) Exclusive prevent access to a resource by concurrent transactions.
- (I) Intent prevent other transactions from modifying the higher-level resource and improve the efficiency of the Database Engine in detecting lock conflicts at the higher level.
- (Sch) Schema uses schema modification (Sch-M) locks during a table data definition language (DDL) operation.
The following table shows the compatibility of the most commonly encountered lock modes.
| Existing granted mode | ||||||
|---|---|---|---|---|---|---|
| Requested mode | IS | S | U | IX | SIX | X |
| Intent shared (IS) | Yes | Yes | Yes | Yes | Yes | No |
| Shared (S) | Yes | Yes | Yes | No | No | No |
| Update (U) | Yes | Yes | No | No | No | No |
| Intent exclusive (IX) | Yes | No | No | Yes | No | No |
| Shared with intent exclusive (SIX) | Yes | No | No | No | No | No |
| Exclusive (X) | No | No | No | No | No | No |
Fantastic website. Lots of helpful information here. I’m sending it to several pals ans also sharing in delicious.
And naturally, thank you for your effort!
Hmm it looks like your site ate my first comment (it was
super long) so I guess I’ll just sum it up what I
submitted and say, I’m thoroughly enjoying
your blog. I as well am an aspiring blog blogger but I’m still new to
everything. Do you have any tips for novice blog writers?
I’d genuinely appreciate it.
Hi, we can talk about it. Feel free to send me an e-mail dccorrea@outlook.com
Perfect Post and website! Congrats, Douglas!
Alex Melo.
CEO GraoDoDia.com