CODEWITHSUNDEEP
javasqlsql-serverlocking
Harold L. Brown
Harold L. Brown

Reputation: 9956

(UPDLOCK, ROWLOCK) locks whole table even tough only 1 row is selected

Inside our Java application we are using a SQL Server statement to pause some processes.

This is the SQL statement:

SELECT * FROM MESSAGES WITH (UPDLOCK, ROWLOCK) 
WHERE MESSAGES.INTERNAL_ID IN ('6f53448f-1c47-4a58-8839-e126e81130f0');

The UUIDs in the IN clause changes of course from run to run.

This the Java code we use for locking:

entityManager.createNativeQuery(sqlString).getResultList()

The above SQL statement returns only one row. Unfortunately it seems that the whole table gets locked. The result is that all processes are locked even though none or only some should be blocked.

Why is the whole table locked even though I specify UPDLOCK?

Additional information:

Upvotes: 2

Views: 2773

Answers (1)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

This is because your MESSAGES.INTERNAL_ID is not a key. Once row is locked you cannot read it and check it's value. Try to create a primary key on this column.

If it's impossible, create INDEX on it and rewrite your query: 

SELECT MESSAGES.INTERNAL_ID FROM MESSAGES WITH (UPDLOCK, ROWLOCK) 
WHERE MESSAGES.INTERNAL_ID IN ('6f53448f-1c47-4a58-8839-e126e81130f0');

MSDN says:

Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

Upvotes: 3

Related Questions