CODEWITHSUNDEEP
cassandracql
Jim Wartnick
Jim Wartnick

Reputation: 2196

Cassandra - How to ensure pk is updated by only one person

Everyone,

I have been an Oracle DBA for over 20 years and am new to Cassandra (just starting to pick it up) - so there's a bit of understanding that I need to adjust to for this. My question is:

Let's assume I have the following table:

CREATE TABLE car_location_index (
    make text,
    model text,
    color text,
    vehical_id int,
    available text,
    lot_id int,
    PRIMARY KEY ((make, model, color), vehical_id))
)

I understand how this is put together from Cassandra's perspective (the wide row on vechial_id based off of make model and color).

My question is: Let's say that this is a real application for a rental car company and the agent wants to find a car for someone. They enter the make, model, and color and find a few with AVAILABLE = 'YES'. How does one go about ensuring that if they "check this car out" to the customer, that another agent does grab the same cell and update AVAILABLE = 'NO' (reservation conflict detection/resolution)?

I don't see any good way to do this (yet) as you can't lock the row (actually would be a cell being locked - ensure nobody else grabs it) and update the AVAILABLE (status) column.

I'm sure this is a very easy question for someone who's worked in Cassandra for a while (as it is a very basic non-issue in, say, oracle), but my RDBMS mind is having a hard time thinking how Cassandra does this.

thanks in advance

-Jim

Upvotes: 2

Views: 57

Answers (1)

Andy Tolbert
Andy Tolbert

Reputation: 11638

You can accomplish this using Lightweight Transactions and the 'IF' clause. This will allow you to conditionally update a row if a condition is met. Depending on whether or not that operation is applied, the result will contains an [applied] column indicating whether or not the operation succeeded and also an additional column indicating the existing value if the operation was not applied.

# create row
cassandra@cqlsh:simple> INSERT into car_location_index (make, model, color, vehical_id, available, lot_id) values ('ford', 'cruise', 'blue', 0, 'YES', 1);

# make car unavailable - should succeed.
cassandra@cqlsh:simple> UPDATE car_location_index set available='NO' where make='ford' and model='cruise' and color='blue' and vehical_id=0 IF available='YES';

 [applied]
-----------
 True

# make care unavailable - won't succeed since already unavailable
cassandra@cqlsh:simple> UPDATE car_location_index set available='NO' where make='ford' and model='cruise' and color='blue' and vehical_id=0 IF available='YES';

 [applied] | available
-----------+-----------
     False |        NO

You can read more about how lightweight transactions work here.

Upvotes: 2

Related Questions