CODEWITHSUNDEEP
mysqlsqldatabaserdbmsacid
user1622801
user1622801

Reputation: 31

SQL - Deduct column value until non-negative value

I working on a table which there is an int column that will get deducted when some event happens. The value is predetermined and cannot goes below zero.

There will be a lot of concurrent SQL transaction to claim the value from the database. It just a counter, like how the concert ticket is reserved.

Currently, my logic cannot guarantee that it stays above 0 in an efficient manner (Select before update cannot guarantee that :( ). Is there a trick to enforce this business rule?

If the value is insufficient, the transaction should not pass.

Upvotes: 0

Views: 958

Answers (3)

danblack
danblack

Reputation: 14666

Solution 1 - use unsigned

ALTER TABLE events MODIFY event_count INT UNSIGNED NOT NULL

Attempting to reduce below 0 will error.

Solution 2 - using the query

UPDATE events SET event_count=event_count-1 WHERE event_id={X} AND event_count > 0

Look at the rows affected to see if this took place.

Upvotes: 1

GMB
GMB

Reputation: 222462

Seems like you neeed some kind of locking mechanism.

That functionality is available in MySQL :

  • InnoDB tables implement row-level locking - see this documentation

  • ISAM only provide table-level locking

Upvotes: 0

Gaurav
Gaurav

Reputation: 1109

You can explore CHECK constraint of MySQL. It will ensure that column value will never go below zero also you will not be required to validate the counter using SQL code.

Sample Table Def :- 

CREATE TABLE IF NOT EXISTS events (
    event_count DECIMAL(10 , 2 ) NOT NULL CHECK(cost >= 0),
    price DECIMAL (10,2) NOT NULL
);

Upvotes: 0

Related Questions