We help IT Professionals succeed at work.
Get Started

SQL SERVER 11.0.5058 Page locks on update

WBSEXPERT
WBSEXPERT asked
on
330 Views
Last Modified: 2015-04-23
The table wbs_nxtno has 1 non-clustered index. We use this table to store key values for order numbers etc. The transaction is creating a page level and row level lock when we update a row. The page level lock is preventing any other user from update a different key value record. How do we lock in row id only, so that different users can obtain and update different key value record. Below are the create table and index command. Along with our test SQL's etc.

CREATE TABLE wbs_nxtno ( wn_system_key     CHAR(10),
                         wn_key_value      INTEGER,
                         wn_usr_id         CHAR(20),
                         wn_user_date      DATE
                        );
CREATE INDEX i_wbs_nxtno ON wbs_nxtno ( wn_system_key, wn_key_value );

ALTER TABLE wbs_nxtno SET (LOCK_ESCALATION=DISABLE);
ALTER INDEX i_wbs_nxtno ON wbs_nxtno SET (ALLOW_PAGE_LOCKS = OFF,ALLOW_ROW_LOCKS = ON);

BEGIN TRANSACTION
update wbs_nxtno
set wn_key_value = wn_key_value
where wn_system_key = 'TPSYSNXSTK'
--COMMIT TRANSACTION

Below is an SQL to show the locks on a specified table:

SELECT
    OBJECT_NAME(P.object_id) AS TableName,
    Resource_type,
    request_session_id
FROM
    sys.dm_tran_locks L
    join sys.partitions P
ON L.resource_associated_entity_id = p.hobt_id
WHERE   OBJECT_NAME(P.object_id) = 'wbs_nxtno'

Results:

TableName  Resource_type  request_session_id
wbs_nxtno   RID                     64
wbs_nxtno   PAGE                  64
Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE