SQL SERVER 11.0.5058 Page locks on update

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
WBSEXPERTAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
With a unique clustering key, SQL should be able to lock a single row for UPDATE, leaving the others available; although not absolutely guaranteed, it should almost always work:

DROP INDEX i_wbs_nxtno ON wbs_nxtno;
CREATE UNIQUE CLUSTERED INDEX wbs_nxtno__CL ON wbs_nxtno ( wn_system_key ) ON [PRIMARY]; --<<-- naturally change the filegroup if/as needed

If that doesn't work, you can force a row to be a full page, which means each row will not page-lock other rows.  It's not pretty, but it should work:

ALTER TABLE wbs_nxtno
ADD force_each_row_to_take_a_full_page varchar(5000) DEFAULT REPLICATE('*', 5000)
0
 
Ramachandar NCommented:
Change your UPDATE statement to
update wbs_nxtno WITH (ROWLOCK)
set wn_key_value = wn_key_value
where wn_system_key = 'TPSYSNXSTK'

Open in new window

0
 
WBSEXPERTAuthor Commented:
Hi Ramachander,

When we change the update statement to what you have suggested we still get 1 PAGE lock and 1 RID lock

TableName      Resource_type      request_session_id
wbs_nxtno      PAGE                      66
wbs_nxtno      RID                              66

Thanks

Matt M
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ramachandar NCommented:
Which means, your table is going for a table scan as it does not have an index on the column or a large number of data is getting updated in the table. Sometimes the optimizer escalates to obtain a PAGE locks based on the load and resource on the server.

What index does the column wn_system_key has?

If you still want it not to obtain a PAGE lock, you can simply turn it off then.
ALTER INDEX index-name ON table-name SET (ALLOW_PAGE_LOCKS = OFF);

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Matt, this is how SQL Server engine works. To keep the data integrity it needs to prevent the data to be changed in more than one place at same time, so it lock the row or the page (depending on the operation costs).
Usually an insert, update or delete command is so fast that the locks won't be an issue. If you are working inside a transaction set the begin and end transaction block as short as possible so the lock can be released as soon as possible.
0
 
WBSEXPERTAuthor Commented:
We went with creating clustered Indexes. We are also revisiting our routines so they use transactions correctly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.