Click here to Skip to main content
14,449,906 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 12-Feb-20 5:22am
Comments
Jörgen Andersson 5 days ago
   
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 yesterday
   
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.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100