Monday, 15 March 2010

Lost Updates

Lost updates happen in multi user environment quite normally if the application is not well built. It happens when you have multiple users and some of them are trying to update same data around same time.

User’s action from query to update: - Data is retrieved to user's memory, make the necessary changes and puts the data back to the database.

Data is read from the database to the users’ machine around the same time, session one makes required changes and saves, by this time the other session also does some changes and saves. If there is no checking the second session will overwrite the first session's changes.

Let’s take an application to explain

This is an application which will be used by users for booking for programmes. The current situation is that only one seat is available but two users are querying the system for booking at the same time.

User_1 queries the system and gets the status of booking “open” only for one person

User_2 queries the system and gets the status of booking “open” only for one person

User_1 books so the status changed to “booked” (no room for new booking)

User_2 still sees the status as “open” on his screen

User_2 books and so the status changes to “booked” and gets saved

Ex:

Don’t forget to set serveroutput on !

--create table for programme booking

CREATE TABLE prg_bookings(envent_id NUMBER,no_booking integer,book_limit integer) ;

--insert a programme with id 1001

INSERT INTO prg_bookings values(1001,9,10);

commit;

CREATE OR REPLACE PROCEDURE PRINT (text VARCHAR2)

IS

BEGIN

DBMS_OUTPUT.put_line (text);

END PRINT;

Run session 1 and session 2 in two different sessions, run session 1 first and then session 2 in less than 10 seconds. In examples I have used dbms lock so that both sessions will see the same data.

Session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 ;

END IF;

IF (SQL%ROWCOUNT) = 1

THEN

PRINT (SQL%ROWCOUNT || ' i have booked');

Commit;

END IF;

END;

Session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 ;

END IF;

IF (SQL%ROWCOUNT) = 1

THEN

PRINT (SQL%ROWCOUNT || ' i have booked');

Commit;

END IF;

END;

Here the booking done by session 1 will be lost. This issue is tracked in two ways

  1. Pessimistic Locking

Pessimistic locking is the mechanism where at the time of user’s intention to update any row, lock that particular row with select ....for update.

ex:

SELECT

no_booking

,book_limit

FROM prg_bookings

WHERE

envent_id = 1001

FOR UPDATE;

  1. Optimistic Locking :- is done in the following ways

Column compare

Hash compare

Timestamp compare

Version compare

Ora_rowscn(new in 10g)

Use this update statement to reset no of booking column
UPDATE prg_bookings SET no_booking = 9 WHERE envent_id = 1001;
commit;



Column compare

Session 1

--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 and no_booking = v_total_booking ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;
END;

Session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

INTO

v_total_booking

,v_booking_limit

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET no_booking= v_total_booking +1

WHERE envent_id = 1001 and no_booking = v_total_booking ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already bookedby someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;

Hash compare (this technique is used by oracle apex)

Timestamp compare

--let's add a column for time stamp comaparison

ALTER TABLE prg_bookings

ADD tim TIMESTAMP;

UPDATE prg_bookings SET no_booking = 9,tim=localtimestamp

WHERE envent_id = 1001;

commit;

session 1
--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_tim_at_qry TIMESTAMP;

BEGIN

SELECT

no_booking

,book_limit

,tim

INTO

v_total_booking

,v_booking_limit

,v_tim_at_qry

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,tim = localtimestamp

WHERE envent_id = 1001 and tim = v_tim_at_qry ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else. Timestamp has been changed,tim stamp at query was ' || v_tim_at_qry );

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;

END;

session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_tim_at_qry TIMESTAMP;

BEGIN

SELECT

no_booking

,book_limit

,tim

INTO

v_total_booking

,v_booking_limit

,v_tim_at_qry

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,tim = localtimestamp

WHERE envent_id = 1001 and tim = v_tim_at_qry ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already bookedby someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;

Version compare
-let us add another column for version compare

ALTER TABLE prg_bookings DROP COLUMN tim;

ALTER TABLE prg_bookings

ADD ver INTEGER;

UPDATE prg_bookings SET no_booking = 9,ver=0 WHERE envent_id = 1001 ;

commit;

session 1

--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_ver PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

,ver

INTO

v_total_booking

,v_booking_limit

,v_ver

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,ver = ver+1

WHERE envent_id = 1001 and ver = v_ver ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else. version changed,version no at query was ' || v_ver );

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;

END;

Session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_ver PLS_INTEGER;

BEGIN

SELECT

no_booking

,book_limit

,ver

INTO

v_total_booking

,v_booking_limit

,v_ver

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

,ver = ver+1

WHERE envent_id = 1001 and ver = v_ver ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already bookedby someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;

Ora_rowscn(new in 10g)

--now lets recreate the table to enable row level scn

drop table prg_bookings;

CREATE TABLE prg_bookings(envent_id NUMBER,no_booking integer,book_limit integer) rowdependencies;

INSERT INTO prg_bookings values(1001,9,10);

commit;

Session 1

--session 1

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_scn_at_query NUMBER;

BEGIN

SELECT

no_booking

,book_limit

,ORA_ROWSCN

INTO

v_total_booking

,v_booking_limit

,v_scn_at_query

FROM prg_bookings

WHERE

envent_id = 1001 ;

--thinking of booking

print('Thinking of Booking!');

DBMS_LOCK.sleep (10);

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

WHERE envent_id = 1001 and ORA_ROWSCN= v_scn_at_query ;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else. Scn changed, Sct at query was ' || v_scn_at_query );

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

END IF;

END;
Session 2

--session 2

DECLARE

v_total_booking PLS_INTEGER;

v_booking_limit PLS_INTEGER;

v_scn_at_query NUMBER;

BEGIN

SELECT

no_booking

,book_limit

,ORA_ROWSCN

INTO

v_total_booking

,v_booking_limit

,v_scn_at_query

FROM prg_bookings

WHERE

envent_id = 1001 ;

--determined to book

print('determined to book!');

IF v_total_booking + 1 <= v_booking_limit THEN

UPDATE prg_bookings

SET

no_booking= v_total_booking + 1

WHERE envent_id = 1001 and ORA_ROWSCN= v_scn_at_query;

END IF;

IF (SQL%ROWCOUNT) = 0

THEN

PRINT ('Id is already booked by someone else');

ELSE

PRINT (SQL%ROWCOUNT || 'i have booked');

commit;

END IF;

END;