How do I force a record lock in SQL server when the default is table lock

Question

0.00/5 (No votes)

See more:

I am using row locking with the SQL Server. I have some locations on SQL Server Express and some on SQL Server 2012 full. I have a table, linked_invoices with a primary key and a ticket_id field. The ticket_id field may be duplicated within the table.

I have a non clustered index (ix_ticket_id) on the ticket_id field.

The query I use to pull the data is

select * from linked_invoices With (rowlock xlock) where ticket_id = 2000074703
I have discovered that with tables that have less than 500 records, the ENTIRE table is locked.

With locations that have more than 500 records, only the records with ticket_id = 2000074703 are locked.

The only solution I have been able to come up with is to add bogus records into the table. Which I hate doing because I am very picky about keeping my database clean.

Has anyone run into this issue and maybe have another solution?

What I have tried:

I have tried forcing the with(index) option to force the query optimizer to use ix_ticket_id but that does not help.

SELECT *
FROM linked_invoices WITH (ROWLOCK XLOCK INDEX (ix_ticket_id))
WHERE ticket_id = 2000084790
I have also tried disabling the lock_escalation ALTER TABLE linked_invoices SET (LOCK_ESCALATION=DISABLE)

I have also tried rebuilding the index ix_ticket_id and turning allow_page_locks off

ALLOW_PAGE_LOCKS = OFF

Posted 12-Feb-20 4:13am

mattielung

Updated 24-Feb-20 8:51am

Add a Solution

Comments

Jörgen Andersson 14-Feb-20 3:04am

The rowlock hint is just exactly that, a hint. The optimizer may choose to ignore it.

The reason for that is that it probably won't use the index anyway when the table is so small that it's only one level deep. So there is basically no performance gain in using an index

That would be especially true if your index isn't covering all the selected columns.
Then it would need to first seek the index and then make a lookup into the table. Which would be twice the amount of operations and a lot slower.

Test it again prefixing the query with SET SHOWPLAN_TEXT ON; and post the resulting plan here.

mattielung 17-Feb-20 10:02am

Here is the plan for a table that has 3 records
|--Clustered Index Scan(OBJECT:([pcsmsLex].[dbo].[linked_invoices].[pk_linked_invoices]), WHERE:([pcsmsLex].[dbo].[linked_invoices].[ticket_id]=(314000334225.)))

Here is the plan from a table that has 500 records.
|--Clustered Index Scan(OBJECT:([pcsmsEugene].[dbo].[linked_invoices].[pk_linked_invoices]), WHERE:([pcsmsEugene].[dbo].[linked_invoices].[ticket_id]=(314000334225.)))

As you can see the plan is the same for both tables.
However, with (rowlock) works differently.

I understand what you mean by the rowlock is just a hint.

However, "with (rowlock)" is useless to me unless I can be sure that it is only locking 1 record, not the entire table.

I do not care about performance gains, I care that my users can edit 1 record in a table without preventing all other users from editing other records in that table.

Jörgen Andersson 21-Feb-20 5:21am

I suspect the difference depends on whether the table fits in one page or not.
If the table fits in one page it will not make a big enough difference what type of lock is taken.
The server always writes a whole page to disc no matter how many rows you write inside the page. This is because of how hard drives work.
When you lock one row, you don't lock it on the hard drive, you lock it on the buffer cache. The changes made still needs to be written to the disc.
And when a page is written to the disc there will be a page lock taken on the buffer to prevent you from changing it while writing.

mattielung 24-Feb-20 11:31am

Thank you for the reply. That fits into my 'solution' for the problem. Adding bogus records into the table will make the table large enough that it will not fit on one page.
It just would have been nice if somewhere in the Microsoft documentation on the option for 'with (rowlock xlock)' it would have said somewhere that you cannot be guaranteed an lock on a single record without locking the entire table.

Jörgen Andersson 24-Feb-20 14:52pm

I made my response an answer so I could add some code

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2020-02-24T08:52:00

Solution 1

I did some testing and I was wrong. It's not about how many pages the table/index is covering.
But it seems to be about how many levels deep the index is.

First I created a few tables of different sizes and checked how many pages they were using this query:

SQL

SELECT  t.NAME AS TableName
       ,p.rows AS RowCounts
       ,SUM(a.total_pages) AS TotalPages
       ,SUM(a.used_pages) AS UsedPages
       ,(SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages
FROM    sys.tables t
JOIN    sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN    sys.partitions p ON i.object_id = p.OBJECT_ID
    AND i.index_id = p.index_id
JOIN    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME = 'MyTableName'
GROUP BY t.Name,p.Rows
ORDER BY t.Name

And I had the same behaviour as you, also on tables covering four pages.

So I checked the indexes of the tables using this procedure:

SQL

SELECT  *
FROM    sys.dm_db_index_physical_stats(DB_ID('MyDatabase'),OBJECT_ID('MyTable'),NULL,NULL,'DETAILED')

And noticed that the indexes of the tables exhibiting this behaviour were all one level deep.
This obviously makes sense since a table scan is the same thing as an index seek or index scan on a clustered table with only one level.
And if the table is unclustered it's even faster.

Posted 24-Feb-20 8:52am

Jörgen Andersson

Comments

mattielung 28-Feb-20 9:27am

Thank you for the response Jorgen.
Just and FYI, I am a vb.net programmer. Out of necessity, I have learned far more about SQL server than I care to.
I think I understand what you mean about 'one level deep' indexes.
Are you suggesting that re-designing the indexes on these tables will help with my record locking issue?

Jörgen Andersson 2-Mar-20 6:26am

No, I'm suggesting that there's not much you can do to force a rowlock in this case. Sql-Server is going to refuse because it tries to keep a balance between the cost of locking vs the cost of concurrency. https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#dynamic_locks
When you only have one level in the index a page/table lock is much more efficient, but to explain why I would need to explain how an index works.
Luckily someone has already done that better than I would: https://use-the-index-luke.com/sql/anatomy/the-tree

mattielung 2-Mar-20 10:07am

Thanks for your input.

I guess I am left with this...
The warning from docs.microsoft.com that explains table hints

"Caution Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators."

means that a later entry in the same document will not guarantee that a row will be locked even though it says'row locks are taken'

"ROWLOCK Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK."

Jörgen Andersson 2-Mar-20 10:30am

Just remembered, there is one more thing you can try:
ALTER TABLE MyTableName SET ( LOCK_ESCALATION = DISABLE);

But use it with caution, if you set it on the wrong table you can easily run out of memory.