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