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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WBSEXPERTAuthor Commented:
We went with creating clustered Indexes. We are also revisiting our routines so they use transactions correctly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.