What you want is SELECT ... FOR UPDATE from within the context of a transaction. SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE. It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to. Just be aware that SELECT ... FOR UPDATE is very bad for concurrency and should only be used when absolutely necessary. It also has a tendency to multiply in a codebase as people cut and paste.
Here's an example session from the Sakila database which demonstrates some of the behaviors of FOR UPDATE queries.
First, just so we're crystal clear, set the transaction isolation level to REPEATABLE READ. This is normally unnecessary, as it is the default isolation level for InnoDB:
session1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
session1> BEGIN;
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA | WILLIAMS |
+------------+-----------+
1 row in set (0.00 sec)
In the other session, update this row. Linda got married and changed her name:
session2> UPDATE customer SET last_name = 'BROWN' WHERE customer_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Back in session1, because we were in REPEATABLE READ, Linda is still LINDA WILLIAMS:
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA | WILLIAMS |
+------------+-----------+
1 row in set (0.00 sec)
But now, we want exclusive access to this row, so we call FOR UPDATE on the row. Notice that we now get the most recent version of the row back, that was updated in session2 outside of this transaction. That's not REPEATABLE READ, that's READ COMMITTED
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3 FOR UPDATE;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA | BROWN |
+------------+-----------+
1 row in set (0.00 sec)
Let's test out the lock set in session1. Note that session2 cannot update the row.
session2> UPDATE customer SET last_name = 'SMITH' WHERE customer_id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
But we can still select from it
session2> SELECT c.customer_id, c.first_name, c.last_name, a.address_id, a.address FROM customer c JOIN address a USING (address_id) WHERE c.customer_id = 3;
+-------------+------------+-----------+------------+-------------------+
| customer_id | first_name | last_name | address_id | address |
+-------------+------------+-----------+------------+-------------------+
| 3 | LINDA | BROWN | 7 | 692 Joliet Street |
+-------------+------------+-----------+------------+-------------------+
1 row in set (0.00 sec)
And we can still update a child table with a foreign key relationship
session2> UPDATE address SET address = '5 Main Street' WHERE address_id = 7;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session1> COMMIT;
Another side effect is that you greatly increase your probability of causing a deadlock.
In your specific case, you probably want:
BEGIN;
SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR UPDATE;
-- do some other stuff
UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>;
COMMIT;
If the "do some other stuff" piece is unnecessary and you don't actually need to keep information about the row around, then the SELECT FOR UPDATE is unnecessary and wasteful and you can instead just run an update:
UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `status`='pending' LIMIT 1;
Hope this makes some sense.
You need to give the query processor a more efficient access path to locate StudentTotalMarks
records. As written, the query requires a full scan of the table with a residual predicate [StudentID] = [@StudentId]
applied to each row:
The engine takes U
(update) locks when reading as a basic defence against a common cause of conversion deadlocks. This behaviour means the second execution blocks when trying to obtain a U
lock on the row already locked with an X
(exclusive) lock by the first execution.
The following index provides a better access path, avoiding taking unnecessary U
locks:
CREATE UNIQUE INDEX uq1
ON dbo.StudentTotalMarks (StudentID)
INCLUDE (StudentMarks);
The query plan now includes a seek operation on StudentID = [@StudentId]
, so U
locks are only requested on target rows:
The index is not required to be UNIQUE
to solve the issue at hand (though the INCLUDE
is required to make it a covering index for this query).
Making StudentID
the PRIMARY KEY
of the StudentTotalMarks
table would also solve the access path problem (and the apparently redundant Id
column could be removed). You should always enforce alternate keys with a UNIQUE
or PRIMARY KEY
constraint (and avoid adding meaningless surrogate keys without good reason).
Best Answer
There are two stages to the lock of updated data. The first is an update lock (U) and the second, provided there is data that needs to be modified, is an exclusive lock (X). It's a two stage operation in the sense that there is data that needs to be searched in order to determine what/if data needs to be modified. The update lock will exist for that (or attempt to be attained for the duration of the operation). Then when data needs to be modified, the update lock will convert into an exclusive lock.
The reason behind this locking mechanism is to prevent deadlocking from the updating data scenario. Please see Kalen Delaney's post on more specifics surrounding this.
Take the below for example:
Execute this for connection 1...
Then in another session, attempt the following update:
What we have here is an update lock that is waiting because there is already an exclusive lock on the resource that the update lock is trying to get (an update lock is not compatible with an exclusive lock). The above scenario is used to "halt" the update lock conversion to exclusive lock.
You can see this by looking at the
sys.dm_tran_locks
DMV:You should have similar results for the output of the above query: