SQL Server Locking Mechanism Irritating

Question

Open 2 windows in SQLServer 2014 Management Studio connected to a 2014 DB.

In 1st window:

SELECT * FROM categorytypes WHERE id = 12

Output:

ID DESCRIPTION ORDER --------------------- 12 Electronics 20

Then:

begin tran UPDATE CategoryTypes SET [order] = 20 WHERE id = 12

Now go to other window (Ctrl+N):

SELECT * FROM CategoryTypes

The query will execute indefinitely until 1st window tran is committed or roll-backed. That's fine because ID=12 row is locked.

SELECT * FROM CategoryTypes WHERE ID <> 12

That works fine.

SELECT * FROM CategoryTypes WHERE Description = 'MEN'

Here it is problem, why the hell this query should execute indefinitely, we know that ID=12 has description 'Electronics'.

In a large application where huge DML process and select operation is done simultaneously on same table, this kind of locking mechanism wont allow to do these 2 things on same time on different set of records.

In Oracle this kind of use case works, as long as locked (dirty row) is not part of result set.

Guys, is there any way to avoid this, kind of Oracle locking mechanism? I don't want to use NOLOCK, or set my transaction to READ UNCOMMITTED.

Thanks.

You have an opportunity to learn about locks here. Take a look at sys.dm_tran_locks to see what locks are being held by your update transaction and what locks are being requested by the select. You might want to join in sys.dm_tran_session_transactions to associate it to a session_id. — Ben Thul
– Ben Thul, Commented Feb 7, 2015 at 5:43
Locking does not really work on rows. It works on index records. Depending on your access path locks might collide or not. — usr
– usr, Commented Feb 7, 2015 at 10:12

Dan Guzman · Accepted Answer · 2015-02-07 04:42:42Z

In SQL Server, the default behavior is to use locking in the default READ_COMMITTED isolation level. With this default locking behavior, it is especially important to have useful indexes so that only needed data are touched. For example, the query with Description in the WHERE clause will not be blocked if you 1) have an index on Description and 2) that index is used in the query plan to locate the needed rows. Without an index on Description, a full table scan will result and the query will be blocked when it hits the uncommitted change.

If you want to avoid locking such that readers don't block writers and visa-versa, you can turn on the READ_COMMITTED_SNAPSHOT database option. SQL Server will then use row versioning instead of locking to ensure only committed data are returned.

Like other DBMS products that use row versioning, there is more overhead with READ_COMMITTED_SNAPSHOT than in-memory locking. SQL Server adds 14 bytes of additional storage per row plus uses tempdb more heavily for the row version store. Whether or not these overhead costs are justified depends on the concurrency benefits your workload experiences.

Collectives™ on Stack Overflow

SQL Server Locking Mechanism Irritating

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related