Concurrently update counter in a mysql MyISAM table

Question

I have a client-server application that send user data to the cloud (Amazon EC2 + RDS + S3).

1. Every user can have multiple devices connecting to the cloud & sending data at the same time
2. Client application installed on each device is multi-threaded and eventually upload multiple data snippets at the same time.

I'd like to reliably track disk usage used in this context and I wonder how to do this in this context?

I have two ideas so far, but I'm not even sure they are correct:

Option 1: Add a trigger to mysql table? ie.

CREATE TRIGGER DiskUsage AFTER UPDATE OF Fully_Updated_File_Flag ON Files
BEGIN
    for each row
    begin
        UPDATE Users SET SpaceUsed = SpaceUsed + new.Size WHERE (new.Fully_Updated_File_Flag = 1) And UserID=
    end
END;

If I opt to use triggers, how am I supposed to dynamically inject the user id?

---

Option 2: Update mysql table via PHP? ie.

<?php

  SendFileToS3($file_name);
  mysql_query('UPDATE Stats SET Value = Value + ' . filesize($file_name) . ' WHERE user_id=' . $user_id);

?>

What if two instances are trying to update the same record? (I'm using Mysql 5.5.27-log / MyISAM), would this still work.

---

Note #1 Although I didn't yet release my application, I still need something that scales well. Even if it means changing db engine all together.

Note #2 DB-related code is encapsulated in modular functions (ie. InsertIntoDB(), UpdateDB() & DeleteFromDB()). Plus all of these routines relies on CodeIgniter 2.1 with active record class.

This is to say that I could always make the switch if I have to (although I'd like to avoid that)

Answer 1

You should use MySQL Triggers instead of PHP code and you have to store the related user_id into diskusage table.

I use InnoDB engine because of the CONSTRAINT. You can also use MyISAM, but you should remove the CONSTRAINT.

REMARK

I would use InnoDB because of Transactions and (more important here) Row-Locking.

Table Structure (InnoDB)

-- ----------------------------
--  Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(10) NOT NULL DEFAULT '',
  `SpaceUsed` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `diskusage`
-- ----------------------------
DROP TABLE IF EXISTS `diskusage`;
CREATE TABLE `diskusage` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Filename` VARCHAR(50) NOT NULL DEFAULT '',
  `Size` BIGINT(20) NOT NULL,
  `user_id` INT(11) UNSIGNED DEFAULT NULL,
  `Fully_Updated_File_Flag` TINYINT(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_diskusage_user` (`user_id`),
  CONSTRAINT `fk_diskusage_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Table Structure (MyISAM)

-- ----------------------------
--  Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(10) NOT NULL DEFAULT '',
  `SpaceUsed` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `diskusage`
-- ----------------------------
DROP TABLE IF EXISTS `diskusage`;
CREATE TABLE `diskusage` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Filename` VARCHAR(50) NOT NULL DEFAULT '',
  `Size` BIGINT(20) NOT NULL,
  `user_id` INT(11) UNSIGNED DEFAULT NULL,
  `Fully_Updated_File_Flag` TINYINT(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_diskusage_user` (`user_id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Thats all, together with some triggers on table diskusage.

INSERT TRIGGER

-- ----------------------------
--  AFTER INSERT TRIGGER for `diskusage`
-- ----------------------------
delimiter ;;
CREATE TRIGGER `diskusage_after_insert` AFTER INSERT ON `diskusage` FOR EACH ROW BEGIN
  IF NEW.Fully_Updated_File_Flag = 1 THEN
    UPDATE users
    SET
      SpaceUsed = SpaceUsed + NEW.Size
    WHERE
      id = NEW.user_id;
  END IF;
END;
 ;;
delimiter ;

UPDATE TRIGGER

-- ----------------------------
--  AFTER UPDATE TRIGGER for `diskusage`
-- ----------------------------
delimiter ;;
CREATE TRIGGER `diskusage_after_update` AFTER UPDATE ON `diskusage` FOR EACH ROW BEGIN

  -- same to DELETE TRIGGER

  -- decrease SpaceUsed with OLD Size for OLD user

  IF OLD.Fully_Updated_File_Flag = 1 THEN
    UPDATE users
    SET
      SpaceUsed = SpaceUsed - OLD.Size
    WHERE
      id = OLD.user_id;
  END IF;

  -- same to INSERT TRIGGER

  -- increase SpaceUsed with NEW Size for NEW user

  IF NEW.Fully_Updated_File_Flag = 1 THEN
    UPDATE users
    SET
      SpaceUsed = SpaceUsed + NEW.Size
    WHERE
      id = NEW.user_id;
  END IF;

END;
 ;;
delimiter ;

DELETE TRIGGER

-- ----------------------------
--  AFTER DELETE TRIGGER for `diskusage`
-- ----------------------------
delimiter ;;
CREATE TRIGGER `diskusage_after_delete` AFTER DELETE ON `diskusage` FOR EACH ROW BEGIN

  IF OLD.Fully_Updated_File_Flag = 1 THEN
    UPDATE users
    SET
      SpaceUsed = SpaceUsed - OLD.Size
    WHERE
      id = OLD.user_id;
  END IF;

END;
 ;;
delimiter ;

Answer 2

If you're tracking files, size & ownership, doing a SELECT SUM(Size) FROM Files WHERE UserID = ? would be blazingly fast on a properly indexed table unless users have a brazillion files associated with them. No need to store a number you can calculate that easily.

Answer 3

Operations for MyISAM tables are atomic. Basically, the queries are automatically committed after they happen.

In addition, UPDATEs are blocking, meaning that only one can occur at a time.

This means that the read-write cycle of the UPDATE will not be interrupted.

MySQL uses table locking for MyISAM tables.

This is fairly quick and will ensure that concurrent updates work correctly.

However, lots of updates may result in the table spending a lot of time being locked. If you have many rows in your table, this may become problematic.

InnoDB tables support row-locking. This takes more resources, but may be much more appropriate if your table gets large. It gives you finer control over the locking and would allow multiple, unrelated, processes to access the table, without excessive lock contention.