Sql-server – How to lock a Row while I’m using it

lockingsql serversql-server-2008

I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table)

I've found this

BEGIN TRAN SELECT * FROM tablename WITH (HOLDLOCK, ROWLOCK)
WHERE ID = 1

My problem is I cant do

UPDATE tablename
SET columnName = -1
WHERE ID = 2

until I commit my previous transaction, why is rowlock locking the whole table?

edit:

Does this code guarantees that the data of the selected row is not update during this update command?

UPDATE [tablename] WITH (ROWLOCK)
SET columnName = columnName + 5
WHERE ID = 1

Best Answer

Based on your edit, all writes lock exclusively.

No other process can read the row(s) being written unless

you've been silly and are using NOLOCK
you have snapshot isolation where the last committed row is read

There is usually no need to add lock hints in day to day code.

As to why you (apparently) have a table lock, this can be caused by no index on ID. The table is being locked because all rows have to be looked at.

Related Solutions

MySQL and PostgreSQL Locking – Does SELECT…FOR UPDATE Release Lock Only on UPDATE?

I want to increment a counter in the selected row, but only if the row's status column is ACTIVE. If it is not ACTIVE, then I just want to release the lock without an update so other threads/processes can read/write that row.

Don't have the SELECT; simply do

UPDATE tbl
    WHERE ...
      AND status = 'active'

The UPDATE will either change the row or do nothing.

MySQL – Will a Transaction Lock the Row?

A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON. It blocks enough to do the query, thereby blocking the other connection. When finished, the other connection proceeds. In all cases, the column is soon decremented by 11.

A third user may see the value decremented by 0 or 4 or 7 or 11. The "very exact time" is not really possible because, at some point in the execution of each statement, a single-threaded lock is checked/set/whatever. That is, they will be serialized, just so fast that you can't see it.

InnoDB locks only rows, not tables. (OK, DDL statement do bolder locks.)

What gets more interesting is a transaction that modifies two things, or that takes a noticeable amount of time:

Intention Case: Single item but taking time:

BEGIN;
SELECT something;
think about it for a while
UPDATE that something;
COMMIT;

The select needs to be written thus:

SELECT something  FOR UPDATE;

This tells other connections "I intend to update the row; please don't mess me up". (I bring up this example, because a lot of newbies miss this subtlety.)

Deadlock case: Messing with 2 things:

BEGIN;    -- in one connection
UPDATE thing_1;
UPDATE thing_2;
COMMIT;

BEGIN;    -- in another connection, at the "exact same time"
UPDATE thing_2;
UPDATE thing_1;
COMMIT;

This is the classic example of a deadlock -- each grabs one thing and then reaches for the other thing. Clearly it can't be made to work. One transaction is killed; the other completes. Hence, you must check for errors, so you can discover it.

The normal reaction to a deadlock is to replay the entire failed transaction. By then, the other connection will not be interfering, and it should proceed without trouble. (OK, yet another connection could create another deadlock.)

Delay Case: If the two connections grab multiple things in the same order, then one can be delayed until the other finishes. To keep this from "waiting forever", there is a default 50-second innodb_lock_wait_timeout. Your pair of simple UPDATEs is actually an example of this case. One will finish promptly; the other is stalled until the first finishes.

Note how a Deadlock can (in some cases) be turned into a Delay by consistently ordering the things you touch.

autocommit=1: With this setting and without calling BEGIN, each statement is effectively:

BEGIN;
your statement
COMMIT;

autocommit=0: This is trouble waiting to happen. When you perform a write query, a BEGIN is implicitly generated. However, it is your responsibility to eventually issue COMMIT. If you fail to do so, you will wonder why your system is hung. (Another common newbie bug.) My advice: "Never use =0".

Best Answer

Related Solutions

MySQL and PostgreSQL Locking – Does SELECT…FOR UPDATE Release Lock Only on UPDATE?

MySQL – Will a Transaction Lock the Row?

Related Question