Delivery Transaction

Delivery Transaction

Input

• w_id = const.

• o_carrier_id = rand [1..10]

• ol_delivery_d = current_system_time

• deffered run: 10 transactions at once

start transaction

"A database transaction is started unless a database transaction is already active from being started as part of the delivery of a previous order (i.e., m ore than one order is delivered within the same database transaction)."

getNewOrder

select *
from NEW-ORDER
where NO_W_ID = w_id
  and NO_D_ID = d_id
order by NO_O_ID asc
limit 1

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "NEW_ORDER"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "NO_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "EQ", "in": 0, "f": "NO_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d}
       ]
    },
    "order": {
      "type": "SortScan",
      "fields": ["NO_O_ID"]
    },
    "project": {
      "type": "ProjectionScan",
      "fields": ["NO_O_ID"]
    }
  },
  "edges" : [["load", "select"], ["select", "order"], ["order", "project"]]
}

deleteNewOrder

delete from NEW_ORDER
where NO_W_ID = w_id
  and NO_D_ID = d_id
  and NO_O_ID = no_o_id

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "NEW_ORDER"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "NO_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "NO_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "EQ", "in": 0, "f": "NO_O_ID", "vtype": 0 /*Int*/, "value": %(no_o_id)d}
       ]
    },
    "delete": {
       "type": "Delete"
    }
  },
  "edges": [["load", "select"], ["select", "delete"]]
}

GetCustomer

select O_C_ID
from ORDER
where O_W_ID = w_id
  and O_D_ID = d_id
  and O_ID = no_o_id
limit 1

{
  "operators": {
    "load": {
       "type": "TableLoad",
       "table": "ORDER",
       "filename": "order.tbl"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": 6 /*And*/},
         {"type": 0 /*Eq*/, "in": 0, "f": "O_W_ID", "vtype": 0 /*Int*/, "value": <w_id>},
         {"type": 6 /*And*/},
         {"type": 0 /*Eq*/, "in": 0, "f": "O_W_ID", "vtype": 0 /*Int*/, "value": <d_id>},
         {"type": 0 /*Eq*/, "in": 0, "f": "O_ID", "vtype": 0 /*Int*/, "value": <no_o_id>}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["O_C_ID"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

update ORDERS
set O_CARRIER_ID = o_carrier_id
where O_ID = no_o_id
  and O_D_ID = d_id
  and O_W_ID = w_id

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ORDERS"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "O_ID", "vtype": 0 /*Int*/, "value": %(no_o_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "O_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "O_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
          "O_CARRIER_ID": %(o_carrier_id)d
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

sumOLAmount

select sum(OL_AMOUNT)
from ORDER-LINE
where OL_W_ID = o_w_id
  and OL_D_ID = o_d_id
  and OL_O_ID = o_id
limit 1

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ORDER_LINE"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "OL_O_ID", "vtype": 0 /*Int*/, "value": %(no_o_id)d}
       ]
    },
    "groupby": {
        "type": "GroupByScan",
        "fields": [],
        "functions": [{"type": "SUM", "field": "OL_AMOUNT"}]
      }
  },
  "edges" : [["load", "select"], ["select", "groupby"]]
}

updateOrderLine

update ORDER_LINE
set OL_DELIVERY_D = <current system time>
where OL_W_ID = o_w_id
  and OL_D_ID = o_d_id
  and OL_O_ID = o_id

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "ORDER_LINE"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_O_ID", "vtype": 0 /*Int*/, "value": %(no_o_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "OL_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
          "OL_DELIVERY_D": %(date)s
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

updateCustomer

update CUSTOMER
set C_BALANCE = C_BALANCE + sum(ol_amount),
    C_DELIVERY_CNT = C_DELIVERY_CNT + 1
where C_W_ID = w_id
  and C_D_ID = d_id
  and C_ID = o_c_id

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "CUSTOMER"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_ID", "vtype": 0 /*Int*/, "value": %(c_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "C_D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d},
         {"type": "EQ", "in": 0, "f": "C_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d}
       ]
    },
    "update": {
       "type": "PosUpdateScan",
       "data": {
          "C_BALANCE": %(ol_total)f
       }
    }
  },
  "edges": [["load", "select"], ["select", "update"]]
}

Commit

Output

TPC