0

I am trying to achieve a task in SQL Server. I'm sharing the sample problem as I couldn't share the entire task description.

Problem: we have a table called Person as follows:

Person_Id   Person_Name   Person_Age
---------   -----------   ----------
        1           AAA           25
        2           BBB           25
        3           CCC           25
        4           DDD           25

From that table, I want to use the Person_Id = 4 that is going to be kept inside a TRANSACTION.

Person_Id   Person_Name   Person_Age
---------   -----------   ----------
        4           DDD           25

While performing the above transaction, user wants to access (INSERT, UPDATE, DELETE) all the other records (other than Person_Id = 4) which are in the table as below:

Person_Id   Person_Name   Person_Age
---------   -----------   ----------
        1           AAA           25
        2           BBB           25
        3           CCC           25

What I tried:

I tried with NOLOCK, ROWLOCK but I couldn't achieve this. Kindly help me to achieve this scenario. I have also tried this link. As per this link, using 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

the SELECT query is fetching the Unmodified data. For example, If I am trying to UPDATE the record in a TRANSACTION and the record got updated but the TRANSACTION is busy with executing other statements.

Person_Id   Person_Name   Person_Age
---------   -----------   ----------
        4           DDD           25

Now, when other connections are trying to SELECT the records in the table, then all other records along with the Record: Person_Id = 4 (With old value) will get returned.

SERIALIZABLE Specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions.

From the above, When I am using SERIALIZABLE isolation, it still returns the OLDER Record of Person_Id = 4. This I don't want in this case.

I want to get all the other records, other than the records in a TRANSACTION.

In other words, If a record(s) is locked in a TRANSACTION, then that record(s) should not appear in any other SELECT STATEMENT EXECUTION with different connections.

Vikash
  • 857
  • 1
  • 13
  • 32
  • What do you mean by keep `Person_Id: 4` inside a transaction? Do you mean you want to exclusively lock that row to prevent all other users from viewing or changing it while reading other rows without blocking others? – Dan Guzman Jul 05 '18 at 17:11
  • 2
    Possible duplicate of [In SQL Server, how can I lock a single row in a way similar to Oracle's "SELECT FOR UPDATE WAIT"?](https://stackoverflow.com/questions/9502273/in-sql-server-how-can-i-lock-a-single-row-in-a-way-similar-to-oracles-select) – geco17 Jul 05 '18 at 17:15
  • create a view for your table? – LONG Jul 05 '18 at 17:43
  • @DanGuzman Yes. You are absolutely correct. Is there any way to do it? – Vikash Jul 05 '18 at 18:55
  • @Vikash, I think you could specify a `WITH (XLOCK, HOLDLOCK)` hint. If you need the other updates be in a different transaction, you'll need to perform that work on a different connection. Not sure what you're actual use case is but you might take a look at a session level application lock so that you don't need to keep a transaction open. Consider the case when a user has the row locked and leaves for the day. – Dan Guzman Jul 06 '18 at 00:50

0 Answers0