Projects > AuctionMark: An OLTP Benchmark for Shared-Nothing Database Management Systems

1. Introduction

AuctionMark benchmark is an Online Transaction Processing (OLTP) workload that simulates the activities found in a well-known auction site. This benchmark comprises of 13 tables and 14 stored procedures representing the auction site’s core transactions.

There are two types of transactions in AuctionMark: (1) transactions that are initiated by a user and (2) book keeping transactions that are invoked periodically by the system. Sellers and buyers are two main user types in the system. Transaction that are related to sellers include (1) adding an item for auction, (2) updating an existing item’s information, and (3) responding to buyers’ comments and feedbacks. Buyers related transactions include (1) adding an item to watch list, (2) retrieving item’s detail, (3) placing a bid on an open auction, (4) purchasing an item, and (5) leaving comments/feedback for sellers. The system periodically invoke check-winning-bid transaction to check if any auction comes to the end, and checks the winning bid for that item.

This benchmark is implemented in Java, and works with H-Store database. It is designed for scalability and flexibility (a user can configure the proportion of different transactions executed).

The source code is publicly available from the H-Store repository.

2. Data Tables

The tables are all named in a singular form. Each column is prefixed with an abbreviation of the table that it is a member of. Foreign key references include the abbreviation of the parent column. The only exception to this rule are those tables with more than one column referencing the same foreign key parent table (e.g., ITEM_COMMENT.ic_u_id and ITEM_COMMENT.ic_buyer_id).

2.1 Global Tables

The Global Tables represent data that is not specific to one particular item or user.

2.1.1 REGION

Number of Records: 62
Average Tuple Size: 28 bytes
This table represents the geographical regions of users.

Column	Type	Cardinality	References	Description
r_id	BIGINT		–	Region’s ID
r_name	VARCHAR	String length 6 – 32	–	Region’s name

2.1.2 GLOBAL_ATTRIBUTE_GROUP

Number of Records: 100
Average Tuple Size: 36 bytes
This table represents merchandises’ global attribute groups (e.g., brand, material, features).

Column	Type	Cardinality	References	Description
gag_id	BIGINT		–	Global attribute group’s ID
gag_c_id	BIGINT		CATEGORY.c_id	Associated Category’s ID
gag_name	VARCHAR	String length 6 – 32	–	Global attribute group’s name

2.1.3 GLOBAL_ATTRIBUTE_VALUE

Number of Records: # GLOBAL_ATTRIBUTE_GROUP * 10
Average Tuple Size: 36 bytes
This table represents merchandises’ global attributes within each attribute groups (e.g., Rolex, Casio, Seiko within brand).

Column	Type	Cardinality	References	Description
gav_id	BIGINT		–	Global attribute group’s ID
gav_gag_id	BIGINT		GLOBAL_ATTRIBUTE_GROUP.gag_id	Associated Category’s ID
gav_name	VARCHAR	String length 6 – 32	–	Global attribute group’s name

2.1.4 CATEGORY

Number of Records: 19,459
Average Tuple Size: 85
This table represents merchandises’ categories. Category can be hierarchical aligned using c_parent_id. The records in this table were retrieved from a well-known

Column	Type	References	Description
c_id	BIGINT	–	Category’s ID
c_name	VARCHAR	–	Category’s name
c_parent_id	BIGINT	CATEGORY.c_id	Parent category’s ID

2.2 User Tables

2.2.1 USER

Number of Records: 1,000,000
Average Tuple Size: 376 bytes
This table represents users of the system both sellers and buyers.

Column	Type	Cardinality	References	Description
u_id	BIGINT		–	User’s ID
u_rating	BIGINT	Integer 0 – 6	–	User’s rating as a seller
u_balance	DOUBLE		–	User’s balance
u_created	TIMESTAMP		–	User’s create date
u_r_id	BIGINT	62	–	User’s region ID
u_sattr0	VARCHAR	String length 16 – 64	–	User’s attribute 0
u_sattr1	VARCHAR	String length 16 – 64	–	User’s attribute 1
u_sattr2	VARCHAR	String length 16 – 64	–	User’s attribute 2
u_sattr3	VARCHAR	String length 16 – 64	–	User’s attribute 3
u_sattr4	VARCHAR	String length 16 – 64	–	User’s attribute 4
u_sattr5	VARCHAR	String length 16 – 64	–	User’s attribute 5
u_sattr6	VARCHAR	String length 16 – 64	–	User’s attribute 6
u_sattr7	VARCHAR	String length 16 – 64	–	User’s attribute 7

2.2.2 USER_ATTRIBUTES

Number of Records: # USER x 1.3
Average Tuple Size: 62 bytes
This table represents attributes of users.

Column	Type	Cardinality	References	Description
ua_id	BIGINT		–	User attribute’s ID
ua_u_id	BIGINT		USER.u_id	User’s ID
ua_name	VARCHAR	String length 5 – 32	–	User attribute name
ua_value	VARCHAR	String length 5 – 32	–	User attribute value
u_created	TIMESTAMP		–	Create timestamps

2.3 Item Tables

2.3.1 ITEM

Number of Records: # USER x 10
Average Tuple Size: 395 bytes
The ITEM id consists of a composite key where the lower 48-bits of the number is the USER.u id and the upper 16-bits is the auction count for that user.

Column	Type	Cardinality	References	Description
i_id	BIGINT		–	Item’s ID
i_u_id	BIGINT		USER.u_id	Seller’s ID
i_c_id	BIGINT		CATEGORY.c_id	Category’s ID
i_name	VARCHAR	String length 6 – 32	–	Item’s name
i_description	VARCHAR	String length 50 – 255	–	Item’s description
i_user_attributes	VARCHAR	String length 20 – 255	–	Text field for attributes defined just for this item
i_initial_price	DOUBLE		–	Item’s initial price
i_current_price	DOUBLE		–	Item’s current price
i_num_bids	BIGINT		–	Item’s number of bids
i_num_images	BIGINT		–	Item’s number of images
i_num_global_attrs	BIGINT		–	Item’s number of global attributes
i_start_date	TIMESTAMP		–	Item’s bid start date
i_end_date	TIMESTAMP		–	Item’s bid end date
i_status	INT		–	Item’s status (0 = OPEN, 1 = WAITING_FOR_PURCHASE, 2 = CLOSED)

2.3.2 ITEM_IMAGE

Number of Records: # ITEM x 3.21
Average Tuple Size: 85 bytes
This table keeps paths to the images of items in the ITEM table.

Column	Type	Cardinality	References	Description
ii_id	BIGINT		–	Image’s ID
ii_i_id	BIGINT		ITEM.i_id	Item’s ID
ii_u_id	BIGINT		ITEM.i_u_id	Item’s user ID
ii_path	VARCHAR	String length 20 – 100	–	Image’s path

2.3.3 ITEM_COMMENT

Number of Records: # ITEM x 0.466
Average Tuple Size: 180 bytes
Comments of items in the ITEM table.

Column	Type	Cardinality	References	Description
ic_id	BIGINT		–	Comment’s ID
ic_i_id	BIGINT		ITEM.i_id	Item’s ID
ic_u_id	BIGINT		ITEM.i_u_id	Item’s user ID
ic_buyer_id	BIGINT		USER.u_id	Item’s buyer ID
ic_date	TIMESTAMP		–	Comment’s create date
ic_question	VARCHAR	String length 10 – 128	–	Comment from buyer
ic_response	VARCHAR	String length 10 – 128	–	Response from seller

2.3.4 ITEM_FEEDBACK

Number of Records: # ITEM x 0.466
Average Tuple Size: 180 bytes
Feedbacks of items in the ITEM table.

Column	Type	Cardinality	References	Description
if_id	BIGINT		–	Feedback’s ID
if_i_id	BIGINT		ITEM.i_id	Item’s ID
if_u_id	BIGINT		ITEM.i_u_id	Item’s user ID
if_buyer_id	BIGINT		USER.u_id	Item’s buyer ID
if_rating	BIGINT		–	Rating given to the seller
if_date	TIMESTAMP		–	Feedback date
if_comment	VARCHAR	String length 10 – 128	–	Feedback from buyer

2.3.5 ITEM_BID

Number of Records: # ITEM x 10.62
Average Tuple Size: 64 bytes
Bids of items in the ITEM table.

Column	Type	References	Description
ib_id	BIGINT	–	Bid’s ID
ib_i_id	BIGINT	ITEM.i_id	Item’s ID
ib_u_id	BIGINT	ITEM.i_u_id	Item’s seller ID
ib_buyer_id	BIGINT	USER.u_id	Buyer’s ID
ib_bid	DOUBLE	–	Bid
ib_max_bid	DOUBLE	–	Max bid
ib_created	TIMESTAMP	–	Bid’s create date
ib_updated	TIMESTAMP	–	Bid’s update date

2.3.6 ITEM_MAX_BID

Number of Records: # ITEM x 0.76
Average Tuple Size: 56 bytes
The maximum bids of each item in the ITEM table.

Column	Type	References	Description
imb_i_id	BIGINT	ITEM.i_id	Item’s ID
imb_u_id	BIGINT	ITEM.i_u_id	Item’s seller ID
imb_ib_id	BIGINT	ITEM_BID.ib_id	Bid’s ID
imb_ib_i_id	BIGINT	ITEM_BID.ib_i_id	Bid’s item ID
imb_ib_u_id	BIGINT	ITEM_BID.ib_u_id	Bid’s seller ID
imb_created	TIMESTAMP	–	Max bid create timestamps
imb_updated	TIMESTAMP	–	Max bid update timestamps

2.3.7 ITEM_PURCHASE

Number of Records: # ITEM x 0.026
Average Tuple Size: 40 bytes
Tthe purchasing records from the buyers.

Column	Type	References	Description
ip_id	BIGINT	–	Purchase’s ID
ip_ib_id	BIGINT	ITEM_BID.ib_id	Bid’s ID
ip_i_id	BIGINT	ITEM_BID.ib_i_id	Item’s ID
ip_u_id	BIGINT	ITEM_BID.ib_u_id	Item’s seller ID
ip_date	TIMESTAMP	–	Purchase timestamps

2.3.8 USER_ITEMS

Number of Records: # ITEM x 0.026
Average Tuple Size: 32 bytes
The items that a user has recently purchased.

Column	Type	References	Description
ui_u_id	BIGINT	USER.u_id	User’s ID
ui_i_id	BIGINT	ITEM.i_id	Item’s ID
ui_seller_id	BIGINT	ITEM.i_u_id	Seller’s ID
ui_created	TIMESTAMP	–	Purchase timestamps

3. Stored Procedures

The stored procedures below are executed in the following distribution except for two stored procedures; check-winning-bid and post-auction. check-winning-bid is executed every 10 seconds, and post-auction is executed right after check-winning-bid.

Stored procedures	Frequency
new-user	5%
new-item	10%
new-bid	18%
new-comment	2%
new-comment-response	1%
new-purchase	2%
new-feedback	3%
get-item	40%
update-item	2%
get-comment	2%
get-user-info	10%
get-watched-item	5%

3.1 new-user

Creates a new USER record. The rating and balance are both set to zero.

The benchmark randomly selects id from a pool of region ids as an input for u_r_id parameter using flat distribution.

Input parameters

Name	Type	Description
u_id	BIGINT	The new user’s unique id
u_r_id	BIGINT	User’s region
attributes[]	8 × VARCHAR(12-64)	An array of random string attributes for this user

Output parameters

None

Pseudo code

// Insert the user
INSERT INTO USER (u_id, u_rating, u_balance, u_created, u_r_id, u_sattr0, u_sattr1, u_sattr2, u_sattr3, u_sattr4, u_sttar5, u_sattr6, u_sattr 7)
VALUES (<u_id>, 0, 0, <current_timestamp>, <u_r_id>, <attributes[0]>, <attributes[1]>, <attributes[2]>, <attributes[3]>, <attributes[4]>, <attributes[5]>, <attributes[6]>, <attributes[7]>);

3.2 new-item

Insert a new ITEM record for a user. The benchmark client provides all of the preliminary information required for the new item, as well as optional information to create derivative image and attribute records. After inserting the new ITEM record, the transaction then inserts any GLOBAL ATTRIBUTE VALUE and ITEM IMAGE. The unique identifier for each of these records is a composite 64-bit key where the lower 60-bits are the i id parameter and the upper 4-bits are used to represent the index of the image/attribute. For example, if the i id is 100 and there are four items, then the composite key will be 0 100 for the first image, 1 100 for the second, and so on. After these records are inserted, the transaction then updates the USER record to add the listing fee to the seller’s balance.

The benchmark randomly selects id from a pool of users as an input for u_id parameter using Gaussian distribution. A c_id parameter is randomly selected using a flat histogram from the real auction site’s item category statistic.

Input parameters

Name	Type	Description
i_id	BIGINT	The new item’s unique id (0–2^60 )
u_id	BIGINT	The seller’s user id
c_id	BIGINT	The new item’s category id
name	VARCHAR	The title of the new item
description	VARCHAR	The text description of the new item
initial_price	DOUBLE	The initial price of the new item
reserve price	DOUBLE	The seller’s reserve selling price (can be null)
buy now	DOUBLE	The seller’s “instance purchase” price (can be null)
attributes	VARCHAR	Textual BLOB of user-defined attributes for the item
gag_ids[]	(0-16) × BIGINT	List of GLOBAL ATTRIBUTE GROUP ids to include for this item
gav_ids[]	(0-16) × BIGINT	List of GLOBAL ATTRIBUTE VALUE ids to include for this item
images[]	(0-16) × VARCHAR(128)	List of unique image paths
start_date	TIMESTAMP	Auction start timestamp
end_date	TIMESTAMP	Auction end timestamp

Output parameters

Name	Type	Description
i_id	BIGINT	The new item’s unique id
i_u_id	BIGINT	The seller’s user id

Pseudo code

// Get attribute names and append them to the item description
description = “”;
for i = 0 to <gag_ids.lenght> – 1
__description += SELECT gag_name + ” ” + gav_name FROM GLOBAL_ATTRIBUTE_GROUP, GLOBAL_ATTRIBUTE_VALUE
______________WHERE gav_id = gav_ids[i] AND gav_gag_id = gag_ids[i] AND gav_gag_id = gag_id;

// Insert the item
INSERT INTO ITEM (i_id, i_u_id, i_c_id, i_name, i_description, i_user_attributes, i_initial_price, i_num_bids, i_num_images, i_num_global_attrs, i_start_date, i_end_date)
VALUES (<i_id>, <u_id>, <c_id>, <name>, <description>, <attributes>, <initial_price>, 0, <images.length>, <gav_ids.length>, <start_date>, <end_date>);

// Add item images
for i = 0 to <images.length> – 1
__ii_id = (i << 60) | (<i_id> & 0x0FFFFFFFFFFFFFFF);
__INSERT INTO ITEM_IMAGE (ii_id, ii_i_id, ii_u_id, ii_path)
__VALUES (<ii_id>, <i_id>, <u_id>, <images[i]>);

// Decrement user balance
UPDATE USER
SET u_balance = u_balance – 1
WHERE u_id = <u_id>;

3.3 new-bid

A buyer enters in a new bidding for an open item. If there is no existing bid on the item, the new bid is marked as the winning bid and the item is updated accordingly. If there is an existing highest bid, then the transaction has to check whether the new bid amount is greater than the existing bid. The id for the new ITEM_BID record is the ITEM.i_num_bids + 1.
Things to check:
• The auction is still open.
• The potential buyer is not the seller.
• The new bid is greater than the current bid.

The benchmark randomly selects ids from a pool of users as an input for u_id and i_buyer_id parameter using Gaussian distribution.

Input parameters

Name	Type	Description
i_id	BIGINT	The item that is being bidded on
u_id	BIGINT	The seller’s user id
i_buyer_id	BIGINT	The buyer’s user id
bid	DOUBLE	The amount the buyer bids on the item
maxBid	DOUBLE	The maximum amount the buyer bids on the item

Output parameters

Name	Type	Description
ib_id	BIGINT	The new bid’s id

Pseudo code

// Increment the number of bids
UPDATE ITEM
SET i_num_bids = i_num_bids + 1
WHERE i_id = <i_id> AND i_u_id = <u_id> AND i_status = 0;

ib_id = SELECT MAX(ib_id) + 1 FROM ITEM_BID WHERE ib_i_id = <i_id> AND ib_u_id = <u_id>;

imb_ib_id = SELECT imb_ib_id FROM ITEM_MAX_BID WHERE imb_i_id = <i_id> AND imb_u_id = <u_id>;

if imb_ib_id is not null then
__// If we have an existing max bid, then we need to figure out whether we are
__// the new highest bidder or if the existing one just has their max_bid bumped up

__current_bid, current_max_bid = SELECT ib_bid, ib_max_bid
____________________________FROM ITEM_BID
____________________________WHERE ib_id = <ib_id> AND ib_i_id = <i_id> AND ib_u_id = <u_id>;

__newBidWin = false;

__if maxBid > current_max_bid then
____newBidWin = true
____if bid < current_max_bid then
______bid = current_max_bid
____endif
__else
____if bid > current_bid then
______UPDATE ITEM_BID
______SET ib_bid = <bid>
______WHERE ib_id = <imb_ib_id> AND ib_i_id = <i_id> AND ib_u_id = <u_id>;
____endif
__endif

__INSERT INTO ITEM_BID (ib_id, ib_i_id, ib_u_id, ib_buyer_id, ib_bid, ib_max_bid, ib_created, ib_updated)
__VALUES (<ib_id>, <i_id>, <u_id>, <i_buyer_id>, <bid>, <maxBid>, <current_timestamp>, <current_timestamp>);

__if newBidWin == true then
____UPDATE ITEM_MAX_BID
____SET imb_ib_id = <imb_ib_id>,
____imb_ib_i_id = <i_id>,
____imb_ib_u_id = <u_id>,
____imb_updated = <current_timestamp>
____WHERE imb_i_id = <i_id> AND imb_u_id = <u_id>;
__endif

else
__// There is no existing max bid record, therefore we can just insert ourselves

__INSERT INTO ITEM_BID (ib_id, ib_i_id, ib_u_id, ib_buyer_id, ib_bid, ib_max_bid, ib_created, ib_updated)
__VALUES (<ib_id>, <i_id>, <u_id>, <i_buyer_id>, <bid>, <maxBid>, <current_timestamp>, <current_timestamp>);

__INSERT INTO ITEM_MAX_BID (imb_i_id, imb_u_id, imb_ib_id, imb_ib_i_id, imb_ib_u_id, imb_created, imb_updated)
__VALUES (<i_id>, <u_id>, <ib_id>, <i_id>, <u_id>, <current_timestamp>, <current_timestamp>);

endif

3.4 new-comment

A potential buyer posts a question/comment about a seller’s item. Each transaction inserts a new ITEM COMMENT record.

The benchmark randomly selects item from a pool of items that are in CLOSED status using flat distribution. It then extracts i_id, seller_id, and buyer_id from the item and associated winning bid as inputs to this stored procedure.

Input parameters

Name	Type	Description
i_id	BIGINT	The item that the potential buyer is commenting on
seller_id	BIGINT	The seller’s user id
buyer_id	BIGINT	The potential buyer’s user id
question	VARCHAR(128)	The new question posted for the item

Output parameters

Name	Type	Description
ic_id	BIGINT	The new comment’s id

Pseudo code

INSERT INTO ITEM_COMMENT (ic_id, ic_i_id, ic_u_id, ic_buyer_id, ic_date, ic_question)
VALUES (
(SELECT MAX(ic_id) FROM ITEM_COMMNET WHERE ic_i_id = <i_id> AND ic_u_id = <seller_id>) + 1,
i_id, buyer_id, <current_timestamp>, question);

3.5 new-comment-response

The seller responds to an open question/comment created by new-comment-response.

The benchmark randomly selects comment from a pool of comments by users using flat distribution. It then uses i_id, i_c_id, and seller_id from the selected comment as inputs to this stored procedure.

Input parameters

Name	Type	Description
i_id	BIGINT	The item
i_c_id	BIGINT	The item comment record that the seller is replying to
seller_id	BIGINT	The seller’s user id
response	VARCHAR(128)	The response to the existing comment/question

Output parameters

None

Pseudo code

UPDATE ITEM_COMMENT
SET ic_response = <response>
WHERE ic_id = <i_c_id> AND ic_i_id = <i_id> AND ic_u_id = <seller_id>

3.6 new-purchase

The buyer purchases the item for a previously won auction. This stored procedure checks first if the input bid is the winning bid. It then inserts a record to the purchase table, and changes item’s status to CLOSED.

The benchmark randomly selects item from a pool of items that are in WAITING_FOR_PURCHASE status using flat distribution. It then gets a wining bid of that item, and extracts ib_id, i_id, u_id, and buyer_id as inputs to this stored procedure.

Input parameters

Name	Type	Description
ib_id	BIGINT	The winning bid ID
i_id	BIGINT	The ID of the purchasing item
u_id	BIGINT	The seller’s user ID
buyer_id	BIGINT	The buyer’s user ID

Output parameters

Name	Type	Description
ip_id	BIGINT	The new purchase id
ip_ib_id	BIGINT	The associated bid’s id
ip_ib_i_id	BIGINT	The associated item’s id
u_id	BIGINT	The associated seller’s user id
ip_ib_u_id	BIGINT	The associated buyer’s user id

Pseudo code

imb_ib_id, imb_ib_i_id, imb_ib_u_id = SELECT imb_ib_id, imb_ib_i_id, imb_ib_u_id
________________________________FROM ITEM_MAX_BID
________________________________WHERE imb_i_id = <i_id> AND imb_u_id = <u_id>;

ib_buyer_id = SELECT ib_buyer_id
____________FROM ITEM_BID
____________WHERE ib_id = <imb_ib_id> AND ib_i_id = <imb_ib_i_id> AND ib_u_id = <imb_ib_u_id>;

// Validate inputs
if ib_id != imb_ib_id || buyer_id != ib_buyer_id then
__throws error
endif

ip_id = (SELECT MAX(ip_id) FROM ITEM_PURCHASE WHERE ip_ib_id = <ib_id> AND ip_ib_i_id = <i_id> AND ip_ib_u_id = <u_id>) + 1;

// Insert a new purchase
INSERT INTO ITEM_PURCHASE (ip_id, ip_ib_id, ip_ib_i_id, ip_ib_u_id, ip_date)
VALUES(<ip_id>, <ib_id>, <i_id>, <u_id>, <current_timestamp>);

// Update item status to closed (i_status = 2)
UPDATE ITEM
SET i_status = 2
WHERE i_id = <i_id> AND i_u_id = <u_id>;

3.7 new-feedback

The buyer adds a seller feedback for a previously won auction. The rating is either -1, 0, or 1 and is added to the USER.u rating attribute.

Input parameters

Name	Type	Description
i_id	BIGINT	The purchased item
seller_id	BIGINT	The seller’s user id
buyer id	BIGINT	The potential buyer’s user id
rating	BIGINT	[−1, 0, 1]
comment	VARCHAR(128)	An optional comment for the seller

Output parameters

Name	Type	Description
if_id	BIGINT	The new feedback’s id

Pseudo code

if_id = (SELECT MAX(if_id) FROM ITEM_FEEDBACK WHERE if_i_id = <i_id> AND if_u_id = <seller_id>) + 1;

INSERT INTO ITEM_FEEDBACK (if_id, if_i_id, if_u_id, if_buyer_id, if_rating, if_date, if_comment)
VALUES(<if_id>, <i_id>, <seller_id>, <buyer_id>, <rating>, <current_timestamp>, <comment>);

3.8 get-item

Get item information. Returns all of the attributes for a particular item.

The benchmark randomly selects item from a pool of items that are in OPEN status using flat distribution. It then extracts i_id and i_u_id from the item as inputs to this stored procedure.

Input parameters

Name	Type	Description
i_id	BIGINT	The id of the item to retrieve
i_u_id	BIGINT	The seller’s user id for this item

Output parameters

Name	Type	Description
i_id	BIGINT	The new item’s unique id
i_u_id	BIGINT	The seller’s user id
i_initial_price	DOUBLE	The initial price of this item
i_current_price	DOUBLE	Current price of this item

Pseudo code

// Get the item
SELECT i_id, i_u_id, i_initial_price, i_current_price
FROM ITEM
WHERE i_id = AND i_u_id = AND i_status = 0;

3.9 update-item

The buyer modifies an existing auction that is still available. The transaction will just update the description of the auction. A small percentage of the transactions will be for auctions that are uneditable; when this occurs, the transaction will abort.

The benchmark randomly selects item from a pool of items that are in OPEN status using flat distribution. It then extracts i_id and i_u_id from the item as inputs to this stored procedure.

Input parameters

Name	Type	Description
i_id	BIGINT	The id of the item to modify
i_u_id	BIGINT	The seller’s user id for this item
description	VARCHAR(255)	The new description for the item

Output parameters

None

Pseudo code

UPDATE ITEM
SET i_description = <description>
WHERE i_id = <i_id> AND i_u_id = <i_u_id>

3.10 check-winning-bids

This stored procedure is called once a minute to examine whether any auctions ended within the last 60 seconds. It returns a list of ITEM.i ids of the auctions that need to be processed. This is a broadcast transaction that must be executed on all nodes.

Input parameters

Name	Type	Description
startTime	TIMESTAMP	Start time of the duration that we want to check for ended auctions
endTime	TIMESTAMP	End time of the duration that we want to check for ended auctions

Output parameters

An array of due item objects each containing the following fields.

Name	Type	Description
i_id	BIGINT	Item’s id
i_u_id	BIGINT	Seller’s id
i_status	BIGINT	Item’s status
imb_ib_id	BIGINT	The winning bid’s id (Can be null if there is no winning bid on this item)
ib_buyer_id	BIGINT	The buyer’s id (Can be null if there is no winning bid on this item)

Pseudo code

// Get all due items that are still in status OPEN
dueItems = SELECT i_id, i_u_id, i_status
_________FROM ITEM
_________WHERE i_start_date BETWEEN AND AND i_status = 0
_________LIMIT 100;

// For each item, check if there is a winning bid
for each dueItem in dueItems
__itemId = dueItem.i_id
__userId = dueItem.i_u_id
__itemStatus = dueItem.i_status

__maxBidId = SELECT imb_ib_id
____________FROM ITEM_MAX_BID
____________WHERE imb_i_id = <itemId> AND imb_u_id = <userId>;

__if maxBidId is not null then

____// Found wining bid, retrieve buyer of the winning bid
____buyerId = SELECT ib_buyer_id
____________FROM ITEM_BID
____________WHERE ib_id = <maxBidId> AND ib_i_id = <itemId> AND ib_u_id = <userId>;

____userItem = {itemId, userId, itemStatus, maxBidId, buyerId}

__else

____userItem = {itemId, userId, itemStatus, null, null}

__endif

__result.add(userItem);

endfor

return result

3.11 post-auction

Each transaction performs post-processing of a given closed auction items. This stored procedure goes through each item and update the item status. If there is no bid for an item, this stored procedure changes the item’s status to closed. Otherwise, the status is changed to waiting-for-purchase, and a new record is inserted to the USER_ITEM table. All IDs of the same item across fields must be on the same array index. This transaction is invoked immediately after check-winning-bid if check-winning-bid finds due items.

Input parameters

Name	Type	Description
i_ids	Arrays of BIGINT	The items’ IDs
seller_ids	Arrays of BIGINT	The sellers’ IDs of the items
buyer_ids	Arrays of BIGINT	The buyers’ IDs of the items
ib_ids	Arrays of BIGINT	The bids’ IDs of the items

Output parameters

Name	Type	Description
closed	BIGINT	The number of CLOSED items processed
waiting	BIGINT	The number of WAITING_FOR_PURCHASE items processed

Pseudo code

// Go through each item and update the item status
// We’ll also insert a new USER_ITEM record as needed
for i = 0 to i_ids.length – 1
__ib_id = ib_ids[i]
__i_id = i_ids[i]
__seller_id = seller_ids[i]
__buyer_id = buyer_ids[i]__
// No bid on this item – set status to close (i_status = 2)
__if ib_id is null then
____UPDATE ITEM
____SET i_status = 2
____WHERE i_id = <i_id> AND i_u_id = <seller_id>;

__// Has bid on this item – set status to wait for purchase (i_status = 1)
__else

____UPDATE ITEM
____SET i_status = 1
____WHERE i_id = <i_id> AND i_u_id = <seller_id>;

____INSERT INTO USER_ITEM (ui_u_id, ui_i_id, ui_i_u_id, ui_created)
____VALUES(<buyer_id>, <i_id>, <seller_id>, <current_timestamp>);

__endif
endfor

3.12 get-comment

Get all item comments of a seller.

The benchmark randomly selects id from a pool of sellers who has items in CLOSED status as a seller_id parameter using flat distribution.

Input parameters

Name	Type	Description
seller_id	BIGINT	The seller’s user ID

Output parameters

An array of comments each containing every field in ITEM_COMMENT table.

Pseudo code

SELECT * FROM ITEM_COMMENT
WHERE ic_u_id = <seller_id> AND ic_response IS NULL;

3.13 get-user-info

Get a user’s items as a seller or a buyer. This stored procedure also retrieves items’ feedbacks if specify. The get_seller_items, get_buyer_items, and get_feedback must be either 0 or 1. If get_seller_items and get_buyer_items are 1 at the same time, this stored procedure will retrieve all items of a user as a seller.

The benchmark randomly selects id from a pool of sellers who has items in OPEN status as a u_id parameter using flat histogram.

Input parameters

Name	Type	Description
u_id	BIGINT	The ID of the user
get_seller_items	BIGINT	1 if get user items as a seller
get_buyer_items	BIGINT	1 if get user items as a buyer
get_feedback	BIGINT	1 if want to include items’ feedback in the response

Output parameters

A user object containing every field in USER table.
An array of item objects each having the following fields.

Name	Type	Description
i_id	BIGINT	Item’s id
i_u_id	BIGINT	Seller’s id
i_name	VARCHAR	Item’s name
i_current_price	DOUBLE	Item’s current price
i_end_date	TIMESTAMP	Item’s auction end date
i_status	INT	Item’s status

An array of item feedback objects each having the following fields.

Name	Type	Description
if_rating	BIGINT	Rating of the item
if_comment	VARCHAR	Feedback of the item
if_date	TIMESTAMP	Date of the feedback
i_id	BIGINT	Item’s id
i_u_id	BIGINT	Seller’s id
i_name	VARCHAR	Item’s name
i_end_date	TIMESTAMP	Item’s auction end date
i_status	INT	Item’s status
u_id	BIGINT	Seller’s id
u_rating	BIGINT	Seller’s rating
u_sattr0	VARCHAR	Seller’s user attribute 0
u_sattr1	VARCHAR	Seller’s user attribute 1

Pseudo code

user_results = SELECT u_id, u_rating, u_balance, u_created, u_sattr0, u_sattr1, u_sattr2, u_sattr3, u_sattr4, r_name
____________FROM USER, REGION
____________WHERE u_id = <u_id> AND u_r_id = r_id;

if get_seller_items == 1 || get_buyer_items == 1 then
__if get_seller_items == 1 then
____item_results = SELECT i_id, i_u_id, i_name, i_current_price, i_end_date, i_status
________________FROM ITEM
________________WHERE i_u_id = <u_id>
________________ORDER BY i_end_date ASC LIMIT 20;
__else if get_buyer_items == 1 then
____item_results = SELECT i_id, i_u_id, i_name, i_current_price, i_end_date, i_status
________________FROM USER_ITEM, ITEM
________________WHERE ui_u_id = <u_id> AND ui_i_id = i_id AND ui_i_u_id = i_u_id
________________ORDER BY i_end_date ASC LIMIT 10;
__endif

__if get_feedback == 1 then
____feedback_results = SELECT if_rating, if_comment, if_date, i_id, i_u_id, i_name, i_end_date, i_status, u_id, u_rating, u_sattr0, u_sattr1
____________________FROM ITEM_FEEDBACK, ITEM, USER
____________________WHERE if_buyer_id = <u_id> AND if_i_id = i_id AND if_u_id = i_u_id AND if_u_id = u_id ” +
____________________ORDER BY if_date DESC LIMIT 10;
__endif
endif

return user_results, item_results, feedback_results

3.14 get-watched-items

Gets all watched items of a buyer.

The benchmark randomly selects id from a pool of users as a u_id parameter using Gaussian distribution.

Input parameters

Name	Type	Description
u_id	BIGINT	The buyer’s user ID

Output parameters

An array of user watched items each having the following fields.

Name	Type	Description
uw_u_id	BIGINT	User’s id
i_id	BIGINT	Item’s id
i_u_id	BIGINT	Seller’s id
i_name	VARCHAR	Item’s name
i_current_price	DOUBLE	Item’s current price
i_end_date	TIMESTAMP	Item’s auction end date
i_status	INT	Item’s status
uw_created	TIMESTAMP	Watched item create date

Pseudo code

SELECT uw_u_id, i_id, i_u_id, i_name, i_current_price, i_end_date, i_status, uw_created
FROM USER_WATCH, ITEM
WHERE uw_u_id = <u_id> AND uw_i_id = i_id AND uw_i_u_id = i_u_id
ORDER BY i_end_date ASC LIMIT 25

Last Updated on October 25th, 2012 by Visawee Angkanawaraphan

Projects > AuctionMark: An OLTP Benchmark for Shared-Nothing Database Management Systems

1. Introduction

2. Data Tables

2.1 Global Tables

2.1.1 REGION

2.1.2 GLOBAL_ATTRIBUTE_GROUP

2.1.3 GLOBAL_ATTRIBUTE_VALUE

2.1.4 CATEGORY

2.2 User Tables

2.2.1 USER

2.2.2 USER_ATTRIBUTES

2.3 Item Tables

2.3.1 ITEM

2.3.2 ITEM_IMAGE

2.3.3 ITEM_COMMENT

2.3.4 ITEM_FEEDBACK

2.3.5 ITEM_BID

2.3.6 ITEM_MAX_BID

2.3.7 ITEM_PURCHASE

2.3.8 USER_ITEMS

3. Stored Procedures

3.1 new-user

Input parameters

Output parameters

Pseudo code

3.2 new-item

Input parameters

Output parameters

Pseudo code

3.3 new-bid

Input parameters

Output parameters

Pseudo code

3.4 new-comment

Input parameters

Output parameters

Pseudo code

3.5 new-comment-response

Input parameters

Output parameters

Pseudo code

3.6 new-purchase

Input parameters

Output parameters

Pseudo code

3.7 new-feedback

Input parameters

Output parameters

Pseudo code

3.8 get-item

Input parameters

Output parameters

Pseudo code

3.9 update-item

Input parameters

Output parameters

Pseudo code

3.10 check-winning-bids

Input parameters

Output parameters

Pseudo code

3.11 post-auction

Input parameters

Output parameters

Pseudo code

3.12 get-comment

Input parameters

Output parameters

Pseudo code

3.13 get-user-info

Input parameters

Output parameters

Pseudo code

3.14 get-watched-items

Input parameters

Output parameters

Pseudo code

Site Information

Latest Development Updates