row-level locking, dirty-read prevention

explicit row-level locking and its uses

----------

MS SQL: (have tested WITH(ROWLOCK) doesn't seems working)

SET @m_Hit = @m_Hit + (SELECT hit FROM topleveldomainhits WITH(TABLOCKX) WHERE tldsuffix = '.com')

SET @m_Hit = @m_Hit + 1


PostgreSQL:

SELECT hit INTO m_Hit FROM topleveldomainhits WHERE tldsuffix = '.com' FOR UPDATE;

m_Hit = m_Hit + 1;


--------------------------------------------------

Note:

Explicit row-Level Locking is not needed if you are not doing complex computation, for the sake of discussion we just make an example of complex hit computation : )

here is a non-complex hit computation:
-- statement A:
UPDATE topleveldomainhits SET hit = hit + 1 WHERE tldsuffix = '.com'

row-level locking is needed if you are doing complex computation,e.g. passing result to variables(likely scenario is doing multi-line computation) and/or the possibility of parallel processing is very high.

if the statement is is one just one line like statement A, and two users executed it in parallel and even you SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, they won't overwrite each other's data. single statements are an atomic operation in itself. so pagehit = 2 :)



-------------------------------
-------------------------------


another example of where explicit row-level locking is necessary, (complex computations)

for the purpose of discussion let's say we have only column field which is Bonus and employeeid



--- bonus computation prodedure run once every month ---

TotalBonus = $80
YearsWork 5
MonthsCount = 3


Final TotalBonus = $1200

MSSQL:

computer A:


CREATE PROCEDURE ComputeYearBonus
    @EmployeeID VARCHAR(8)
AS
    SET XACT_ABORT ON

    BEGIN TRAN
    DECLARE @YearsWork INT

    SELECT @YearsWork = YearCount FROM YearsWork WITH(TABLOCKX) WHERE Employee =
@EmployeeId

    DECLARE @Bonus MONEY
    -- process 1. read bonus = 80 * 5 = $400
    SET @Bonus = (SELECT TotalBonus FROM Employee WITH(TABLOCKX) WHERE EmployeeId = @EmployeeId) * @YearsWork

    -- process 4. write bonus = $400
    UPDATE Employee SET TotalBonus = @Bonus WHERE EmployeeId = @EmployeeID

    COMMIT TRAN
GO

computer B:


CREATE PROCEDURE ComputeMonthBonus
    @EmployeeID VARCHAR(8)

AS
    SET XACT_ABORT ON
    BEGIN TRAN

    DECLARE @MonthsWork INT
    SELECT @MonthsWork = MonthCount FROM MonthsWorkThisYear WITH(TABLOCKX) WHERE Employee = @EmployeeId

    DECLARE @Bonus MONEY
   
    -- process 2. read bonus = 80 * 3 = $240    
    SET @Bonus = (SELECT TotalBonus FROM Employee WITH(TABLOCKX) WHERE EmployeeId = @EmployeeId) * @MonthsWork

    -- process 4. write bonus = $240
    UPDATE Employee SET TotalBonus = @Bonus WHERE EmployeeId = @EmployeeID

    COMMIT TRAN

GO

without WITH(TABLOCKX), when computer A and computer B execute at the same time, there is a high probability that they will overwrite each other's bonus computation. see the steps above. last written bonus is $240 instead of $1200.

if we put WITH(TABLOCKX) in our SELECTs, process 2 will wait until computer A commits. after computer A commits, computer B will read the bonus as $400, then $400 multiply by $3 = $1200.

same thing would effect even if computer B executes first, computer B computes $240. then computer A would read the bonus, computer A can't read the bonus until computer B commits. after B commits, computer A will read the bonus of $240. then computer A will multiply this by 5. $240 * 5 = $1200.


you'll notice that in the YearsWork in MonthsWorkThisYear we also put TABLOCKX, same thing, if there are other parallel executing transaction or trigger that updates the two table at the same time with our ComputeBonus. the computebonus will be messed, there will be dirty reads if there is no row-locking, we can't afford to have our bonus miscomputed.


------------------------------------------------

PostgreSQL Version:


postgresql has implicit BEGIN TRAN AND COMMIT TRAN:



CREATE FUNCTION ComputeYearBonus() RETURNS VOID
    m_EmployeeID VARCHAR(8)
AS
$$
DECLARE
    m_YearsWork INT;
    m_Bonus NUMERIC;
BEGIN


    SELECT YearCount INTO m_YearsWork  FROM YearsWork WHERE Employee = m_EmployeeId FOR UPDATE;

    -- process 1. read bonus = 80 * 5 = $400
    m_Bonus = (SELECT TotalBonus FROM Employee WHERE EmployeeId = m_EmployeeId FOR UPDATE) * m_YearsWork;


    -- process 4. write bonus = $400
    UPDATE Employee SET TotalBonus = m_Bonus WHERE EmployeeId = m_EmployeeID;

END;
$$
LANGUAGE 'plpgsql';




CREATE FUNCTION ComputeMonthBonus() RETURNS VOID
    m_EmployeeID VARCHAR(8)

AS
$$
DECRLARE
    m_MonthsWork INT
    m_Bonus NUMERIC
BEGIN


    SELECT MonthCount INTO m_MonthsWork FROM MonthsWorkThisYear WHERE Employee = m_EmployeeId FOR UPDATE;


    -- process 2. read bonus = 80 * 3 = $240    
    m_Bonus = (SELECT TotalBonus FROM Employee WHERE EmployeeId = m_EmployeeId FOR UPDATE) * m_MonthsWork;


    -- process 4. write bonus = $240
    UPDATE Employee SET TotalBonus = m_Bonus WHERE EmployeeId = m_EmployeeID;

END;
$$
LANGUAGE 'plpgsql';