Db Locking

@sharkzp 2014

Types:

• Two-phase locking (2PL)

DB2, SQL Server, CUBRID, InnoDB

• Multi-version concurrency control (MVCC)

Oracle, PostrgeSQL

2PL

acquired locks(read/write) while executing a transaction, release all locks only after the transaction ends (commit or rollback)

Example

Transaction 1: B = B+A

Transaction 2: C = A+B

Transaction 3: print C

S - shared lock or read lock

X - exclusive lock or write lock

Deadlock example

Try it live!

mysql -u root -p
use STORE_TEST;
CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);
START TRANSACTION;
SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;

DELETE FROM t WHERE i = 1;

mysql -u root -p
use STORE_TEST;
START TRANSACTION;
DELETE FROM t WHERE i = 1;

Title Text

• http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html
• http://www.cubrid.org/blog/cubrid-life/all-about-two-phase-locking-and-a-little-bit-mvcc/
• http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html
•  

Multi-version concurrency control

This

Became

Updating record

Updating in progress

Updating...

Updated

Insert

Inserted

Delete

Deleted

How to use locks

Code that does not works

class CheckShipmentStatus
  include Sidekiq::Worker

  sidekiq_options queue: :low, retry: 10, throttle: { threshold: 10, period: 1.second }

  @queue = :shipments
  def perform(shipment_id)
    shipment = Shipment.find(shipment_id)
    MailQueue.shipment_completed_email(shipment)
  end
end

Code that works

class CheckShipmentStatus
  include Sidekiq::Worker

  sidekiq_options queue: :low, retry: 10, throttle: { threshold: 10, period: 1.second }

  @queue = :shipments
  def perform(shipment_id)
    shipment = Shipment.find(shipment_id)
    shipment.with_lock do
      MailQueue.shipment_completed_email(shipment)
    end
  end
end

Problems that could be faced

• Deadlocks
• Replication/sharding