Row Locks - Manually Using Them

Row locks - manually using them

The kind of approach I'd recommend is to have a field in the record along the lines of that indicates whether the record is being processed or not. Then implement a "read next from the queue" sproc that does the following, to ensure no 2 processes pick up the same record:

BEGIN TRANSACTION

-- Find the next available record that's not already being processed.
-- The combination of UPDLOCK and READPAST hints makes sure 2 processes don't 
-- grab the same record, and that processes don't block each other.
SELECT TOP 1 @ID = ID
FROM YourTable WITH (UPDLOCK, READPAST)
WHERE BeingProcessed = 0

-- If we've found a record, set it's status to "being processed"
IF (@ID IS NOT NULL)
    UPDATE YourTable SET BeingProcessed = 1 WHERE ID = @ID

COMMIT TRANSACTION

-- Finally return the record we've picked up
IF (@ID IS NOT NULL)
    SELECT * FROM YourTable WHERE ID = @ID

For more info on these table hints, see MSDN

Row locks - manually using them

The kind of approach I'd recommend is to have a field in the record along the lines of that indicates whether the record is being processed or not. Then implement a "read next from the queue" sproc that does the following, to ensure no 2 processes pick up the same record:

BEGIN TRANSACTION

-- Find the next available record that's not already being processed.
-- The combination of UPDLOCK and READPAST hints makes sure 2 processes don't 
-- grab the same record, and that processes don't block each other.
SELECT TOP 1 @ID = ID
FROM YourTable WITH (UPDLOCK, READPAST)
WHERE BeingProcessed = 0

-- If we've found a record, set it's status to "being processed"
IF (@ID IS NOT NULL)
    UPDATE YourTable SET BeingProcessed = 1 WHERE ID = @ID

COMMIT TRANSACTION

-- Finally return the record we've picked up
IF (@ID IS NOT NULL)
    SELECT * FROM YourTable WHERE ID = @ID

For more info on these table hints, see MSDN

How to release possible Postgres row locks?

What version of PostgreSQL are you running? The following assumes 8.1.8 or later (it may apply to earlier versions too, I don't know).

I presume that you mean that phpPgAdmin timed out -- the PostgreSQL backend will take as long as it takes to complete a query/update. In that case, it's possible that the original session is still alive and the UPDATE query is still running. I suggest running the following query (taken from chapter 24 of the PostgreSQL docs) on the machine that hosts the PostgreSQL server process, to see whether the session is still alive:

ps auxwww|grep ^postgres

Several rows should appear: 1 for the postmaster master process, and 1 each for "writer", "stats buffer", and "stats collector" processes. Any remaining lines are for processes serving DB connections. These lines will contain the username and database name.

Hopefully, from that you can see whether the session you performed the original UPDATE in is still hanging around. Although in theory you could find more detailed info by SELECTing from the system view pg_stat_activity, by default PostgreSQL is not set up to populate the most useful fields (such as current_query and query_start). See chapter 24 for how to enable this in the future.

If you see the session is still there, kill it. You will need to be logged in as the user running the process (usually postgres) or root to do so -- if you don't run the server yourself, get your DBA to do this for you.

One more thing: for updating rows in a table, PostgreSQL avoids using locks. Instead, it allows every writing transaction to create a new "version" of the DB, which becomes the "current version" when the transaction is committed, provided it doesn't conflict with updates made in the meantime by other transactions. So I suspect the "hanging" you're seeing is caused by something else -- though what, I'm not sure. (Have you checked the obvious things, like whether the disk partition containing the DB is full?)

How can I lock a single row in Oracle SQL

You may lock the record as described in other answers, but you will not see any exception while UPDATEing this row.

The UPDATE statement will wait until the lock will be released, i.e. the session with SELECT ... FOR UPDATE commits. After that the UPDATE will be performed.

The only exeption you can manage is DEADLOCK, i.e.

Session1   SELECT FOR UPDATE record A
Session2   SELECT FOR UPDATE record B
Session1   UPDATE record B  --- wait as record locked
Session2   UPDATE record A  --- deadlock as 1 is waiting on 2 and 2 waiting on 1

In SQL Server, how can I lock a single row in a way similar to Oracle's SELECT FOR UPDATE WAIT?

In SQL Server there are locking hints but they do not span their statements like the Oracle example you provided. The way to do it in SQL Server is to set an isolation level on the transaction that contains the statements that you want to execute. See this MSDN page but the general structure would look something like:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    select * from ...

    update ...

COMMIT TRANSACTION;

SERIALIZABLE is the highest isolation level. See the link for other options. From MSDN:

SERIALIZABLE Specifies the following:

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

No other transactions can modify data that has been read by the
current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would
fall in the range of keys read by any statements in the current
transaction until the current transaction completes.

Is there a way to manually lock/unlock a table row in PostgreSQL that does not rely on transactions?

If you need to persist that information across transactions and sessions, then yes, you need a column that stores that information.

I wouldn't use a boolean though, but a nullable timestamp column so that you can see when the row was locked (null means "not locked", not null means "locked"). This is very useful to cleanup "abandoned locks".

---

Related Topics