0

How can I lock a table row in SQL Server? If I use transaction it locks the whole table. But I need to lock only a particular row.

Ex.

Begin tran

Select Accountbalance from User_table where userid=934352

Commit tran

If I execute this query this row should lock. This record should not read any other transaction, but other user can read. We need to lock 934352 userId only .

Thanks, Somu

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2354274
  • 187
  • 5
  • 16
  • how about this answer http://stackoverflow.com/a/386301/1692632 – Darka Mar 18 '14 at 10:18
  • Check this article http://www.sqlteam.com/article/introduction-to-locking-in-sql-server – Naveen Mar 18 '14 at 10:21
  • 1
    a quick search in SO: http://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server – Ricardo Appleton Mar 18 '14 at 10:22
  • 1
    The default mode is *NOT* table lock, unless you've changed it somehow (eg by specifying a different isolation level). Moreover, no lock is kept beyond the end of a transaction. What are you trying to do? What is the problem that you think will be solved by using transactions? – Panagiotis Kanavos Mar 18 '14 at 10:38
  • 1
    Moreover, a READ lock doesn't prevent anyone else from reading the same row - nor should it. Perhaps you want to do something else but use the wrong terms? Are you looking for a way to checkout/checkin rows? – Panagiotis Kanavos Mar 18 '14 at 10:40

2 Answers2

1

As said before, a lock does not stop the user reading the line.

Perhaps you could add a column to your table that is a bit, ie, 1 or 0. You could then have this bit set to 1 if you want it viewable, 0 if you don't want people to see it, you could then implement a view with only selects rows from the database which have a bit value of 1.

For example

I create a table called Employees which looks like this

Id |  Name  |    Address   | Salary | IsViewable
---|--------|--------------|--------|------------
 1 | Bloggs | Fake address | 50000  |    0
 2 | Parker | Fake address | 17000  |    1

You would then create a view with something like the following select statement...

SELECT Id, Name, Address, Salary
FROM Employees
WHERE IsViewable = 1

I hope this covers sort of what you're asking. This way you are able to stop people viewing lines that you don't want people to see. Additionally, you could lock that particular row if you wanted to as well, but really there would be no point.

Joe Harper
  • 470
  • 2
  • 11
  • It appears as though the OP needs application logic to temporarily 'reserve' stock during checkout. This answer best addresses this requirement. A database-level row lock isn't a suitable device for this scenario. – Steve Martin Mar 18 '14 at 15:36
0

I think you can do this with RowLock, but I would be careful about it.
The query would look like this then:

BEGIN TRAN
    SELECT Accountbalance 
    FROM User_table 
    WITH (HOLDLOCK, ROWLOCK)
    WHERE userid=934352
COMMIT TRAN
seph
  • 674
  • 2
  • 8
  • 23
  • No, this is not working. I can able to read same record in another transaction. – user2354274 Mar 18 '14 at 10:26
  • Means? i just ran that script. and I checked this select query in another transaction. it's returning balance. Can we set any settings?. I should get balance in another transaction this first transaction should commit or rollback. that's it – user2354274 Mar 18 '14 at 10:39
  • @user2354274 sounds like you misunderstand what locking does. A read lock does *NOT* hide the row. – Panagiotis Kanavos Mar 18 '14 at 10:41
  • ok.Ex. we have only 2kg stock. one user bought that 2kg. we are checking product stock and allowing to purchase. another user going to buy same product 2kg. Now we should not allow to read that product right. if we allow it will return 2kg. so only we need to lock that row until commit or roll back executed. – user2354274 Mar 18 '14 at 10:53
  • See if setting the transaction isolation level to Read Uncommitted helps. – Elmer Mar 18 '14 at 11:31