CODEWITHSUNDEEP
sql-serverjpaspring-data-jpa
curious1
curious1

Reputation: 14737

Simultaneous reads and writes on different rows of the same table in SQL Server

My web application runs on Spring (MVC) 4.2.9.RELEASE, Hibernate 5.1.3.Final, Spring Data 1.8.2.RELEASE, and MS SQL Server Enterprise 2014 (two clustered virtual machines and AlwaysOn).

The application frequently reads and writes on one table. However, all reads and writes always happen on different rows (records). Put it another way, there is never a time when two or more reads or writes happen on the same row at the same time.

How can I configure this table through JPA to make sure that a write or read on a row in this table will not block other simultaneous reads and writes on the same table? For example, two writes can perform on the table at the same time since they never writes on the same row. Basically, I would like to increase performance of the website and reduce chances of deadlocks.

Is this something doable through JPA?

Upvotes: 0

Views: 841

Answers (1)

Andomar
Andomar

Reputation: 238256

Locking is quite complicated. Likely, SQL Server locks your table per page. That means writes to one page do not block reads to other pages.

A best practice is to monitor the logs for deadlocks. If no deadlocks occur, there's no need to customize locking. If deadlocks do occur, you'll have a specific problem to solve. A solution can involve modifying your queries with table hints, configuring SQL Server, or changing the logic. But that's impossible to tell without knowing the details.

Upvotes: 1

Related Questions