I am facing an issue with concurrent ticket bookings. When two or more users try to book the same ticket at the same time (for the same time slot), the system is allowing both bookings, leading to overbooking.
The issue occurs when the same ticket is added to the cart by different users or sessions simultaneously. The code does not seem to handle concurrent bookings correctly, resulting in some users successfully booking tickets that should have been unavailable due to prior bookings.
<?php
function custom_remove_exhausted_booking_items()
{
$filePath = EVENT_BOOKING_PLUGIN_DIR . 'order.txt';
$cronStartAt = date('Y-m-d H:i:s');
file_put_contents($filePath, "[Order - Start at- " . $cronStartAt . "\n", FILE_APPEND);
global $wpdb;
if (is_user_logged_in()) {
$user_id = get_current_user_id();
$booking_data_json = get_user_meta($user_id, 'booking_data', true);
$booking_data = json_decode($booking_data_json, true);
} else {
$booking_data = WC()->session->get('booking_data');
}
$time_slot_id = $booking_data['time_slot_id'] ?? null;
$time_slot = $booking_data['time_slot'] ?? null;
$product_id = $booking_data['product_id'] ?? null;
$booking_date = $booking_data['booking_date'] ?? null;
$time_slot_details = $wpdb->get_row(
$wpdb->prepare(
"SELECT from_time, to_time FROM {$wpdb->prefix}et_bk_event_timeslots WHERE id = %d",
$time_slot_id
)
);
if ($time_slot_details) {
if (false === ($bookingCreation = get_transient('bookingCreation'))) {
$exhausted = false;
// $wpdb->query("LOCK TABLES {$wpdb->prefix}et_bk_bookings WRITE, {$wpdb->prefix}et_bk_bookings_details WRITE");
$wpdb->query('START TRANSACTION');
// Insert into event_ticket_bookings
$inserted = $wpdb->insert(
$wpdb->prefix . 'et_bk_bookings',
array(
'order_id' => 0,
'time_slot_id' => $time_slot_id,
'status' => 'pending'
),
array(
'%d',
'%s',
'%s',
)
);
if ($inserted) {
$booking_id = $wpdb->insert_id;
if (is_user_logged_in()) {
$user_id = get_current_user_id();
update_user_meta($user_id, 'booking_id', $booking_id);
} else {
WC()->session->set('booking_id', $booking_id);
}
foreach (WC()->cart->get_cart() as $cart_item_key => $cart_item) {
$current_quantity = $cart_item['quantity'];
$time_slot_id = isset($cart_item['time_slot_id']) ? $cart_item['time_slot_id'] : null;
$et_bk_event_ticket_types_dates_id = isset($cart_item['et_bk_event_ticket_types_dates_id']) ? $cart_item['et_bk_event_ticket_types_dates_id'] : null;
if (! $time_slot_id || ! $et_bk_event_ticket_types_dates_id) {
continue;
}
$ticket_types = $wpdb->get_results("
SELECT tt.*, ttd.id AS et_bk_event_ticket_types_dates_id, ttd.date, ttd.price AS date_price, ttd.quantity AS date_quantity, ttd.deposit_amount AS date_deposit
FROM {$wpdb->prefix}et_bk_event_ticket_types tt
LEFT JOIN {$wpdb->prefix}et_bk_event_ticket_types_dates ttd ON tt.id = ttd.et_bk_event_ticket_types_id
WHERE tt.et_bk_event_timeslots_id = $time_slot_id
AND ttd.id = $et_bk_event_ticket_types_dates_id
ORDER BY tt.sort_order ASC ");
if (empty($ticket_types)) {
continue;
}
foreach ($ticket_types as $ticket) {
$booked_quantity = $wpdb->get_var(SQL_BlockedQty($ticket->et_bk_event_ticket_types_dates_id, $product_id));
file_put_contents($filePath, "\nSQL_BlockedQty" . SQL_BlockedQty($ticket->et_bk_event_ticket_types_dates_id, $product_id), FILE_APPEND);
file_put_contents($filePath, "\nbooked_quantity : {$booked_quantity} ", FILE_APPEND);
$booked_quantity += $current_quantity;
file_put_contents($filePath, "\tbooked_quantity_after: {$booked_quantity} \t Cur Qty : {$ticket->date_quantity}", FILE_APPEND);
if ($booked_quantity > $ticket->date_quantity) {
WC()->cart->remove_cart_item($cart_item_key);
$exhausted = true;
}
}
if (!$exhausted) {
$ticket_type_name = $cart_item['ticket_type_name'];
$et_bk_event_ticket_types_dates_id = $cart_item['et_bk_event_ticket_types_dates_id'];
// Insert multiple entries in event_ticket_bookings_details
$wpdb->insert(
$wpdb->prefix . 'et_bk_bookings_details',
array(
'et_bk_bookings_id' => $booking_id,
'et_bk_event_ticket_types_dates_id' => $et_bk_event_ticket_types_dates_id,
'ticket_type_name' => $ticket_type_name,
'quantity' => $cart_item['quantity']
),
array(
'%d',
'%d',
'%s',
'%d'
)
);
}
file_put_contents($filePath, "\n{$wpdb->last_query} ", FILE_APPEND);
}
}
if ($exhausted) {
file_put_contents($filePath, "\nROLLBACK\n\n", FILE_APPEND);
$wpdb->query('ROLLBACK');
throw new Exception('Some items in your cart are no longer available for booking. Please review your cart and try again. <script>setTimeout(() => { location.reload(); }, 1000);</script>');
} else {
file_put_contents($filePath, "\nCOMMIT\n\n", FILE_APPEND);
$wpdb->query('COMMIT');
}
// $wpdb->query("UNLOCK TABLES");
$bookingCreation = 'bookingCreation';
set_transient('bookingCreation', $bookingCreation);
}
} else {
return;
}
}
The problem occurs when multiple users or sessions try to book the same ticket for the same time slot simultaneously. The code does not seem to properly manage the race condition for concurrent bookings, allowing users to book more tickets than available.
I have logged order.txt file
[Order - Start at- 2024-10-10 06:00:41
[Order - Start at- 2024-10-10 06:00:41
SQL_BlockedQty
SELECT SUM(bd.quantity)
FROM wp_et_bk_bookings b
INNER JOIN wp_et_bk_bookings_details bd ON b.id = bd.et_bk_bookings_id
WHERE bd.et_bk_event_ticket_types_dates_id = 17
AND b.product_id = 38
AND b.status IN ('pending', 'processing', 'on-hold', 'completed')
booked_quantity : booked_quantity_after: 8 Cur Qty : 10
INSERT INTO `wp_et_bk_bookings_details` (`et_bk_bookings_id`, `et_bk_event_ticket_types_dates_id`, `ticket_type_name`, `quantity`) VALUES (1, 17, 'VIP 4:15 PM', 8)
COMMIT
SQL_BlockedQty
SELECT SUM(bd.quantity)
FROM wp_et_bk_bookings b
INNER JOIN wp_et_bk_bookings_details bd ON b.id = bd.et_bk_bookings_id
WHERE bd.et_bk_event_ticket_types_dates_id = 17
AND b.product_id = 38
AND b.status IN ('pending', 'processing', 'on-hold', 'completed')
booked_quantity : booked_quantity_after: 7 Cur Qty : 10
INSERT INTO `wp_et_bk_bookings_details` (`et_bk_bookings_id`, `et_bk_event_ticket_types_dates_id`, `ticket_type_name`, `quantity`) VALUES (2, 17, 'VIP 4:15 PM', 7)
COMMIT
[Order - Start at- 2024-10-10 06:04:26
[Order - Start at- 2024-10-10 06:04:26
I have tried Table Lock but it not generated single booking.
Please could any once suggest strategy for this case?