Shulou(Shulou.com)05/31 Report--

This article introduces you how to use the database to achieve a reservation function, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Part one: the realization of SKIP LOCKED/NOWAIT reservation function

Booking is a very common scene in real life, such as the choice of train ticket seats, cinema seats and so on. So how to realize the reservation function? An application may be implemented in many different ways, of course, without a database. Here we will introduce an implementation of pure database.

Imagine that we have a seating table as follows:

CREATE TABLE seats (seat_no INT PRIMARY KEY, booked ENUM ('YES',' NO') DEFAULT 'NO') ENGINE=InnoDB

There are 100 seats in the table, from 0 to 99. For example, if we want to reserve seats for 2p3, we can start the business first and lock the seats:

START TRANSACTION;SELECT * FROM seats WHERE seat_no IN (2 and 3) AND booked = 'NO' FOR UPDATE

SELECT... The FOR UPDATE statement returns results in the following three cases:

1. If success is returned, and the result set contains 2 and 3, then the lock is successful. We can proceed to the next step, wait for the payment to be completed, update the seat status and submit the transaction, and the reservation is completed. UPDATE seats SET booked = 'YES' WHERE seat_no IN (2pm 3) COMMIT

two。 Success is returned, but the result set is empty, or contains only 2 or 3, which means the lock failed.

3. Do not return for a long time until the return times out. For example, seat 2 or 3 has been locked by another transaction and is waiting for the payment to be completed or something else happens, resulting in the transaction not being committed (commit) or rolled back (rollback). By default, you need to wait for 50 seconds to return the timeout. We can configure a reasonable wait time by modifying the innodb_lock_wait_timeout parameter. The error returned after timeout is as follows: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Case 3 means stuck to the user, which is totally unacceptable. Why did the wait happen? In InnoDB's lock system (lock system), if seat 2 is locked by an X (write lock) or IX (intended update lock) by a transaction, then the next transaction that will lock seat 2 with an X or IX lock will have to wait. This is determined by the ACID of the transaction itself.

So is there a way to avoid waiting and possible timeouts later? SKIP LOCKED/NOWAIT, the new feature provided by MySQL 8.0, is fine. SKIP LOCKED means to skip seats that have been locked in by other transactions. Use the following SKIP LOCKED statement for seat locking, and the returned result set may be empty, 2 or 3, 2 and 3. When the result set is not empty, the returned seat is locked successfully.

SELECT * FROM seats WHERE seat_no IN (2 and 3) AND booked = 'NO'FOR UPDATE SKIP LOCKED

NOWAIT means that if you encounter a seat locked by another transaction, do not wait and return an error directly. If you use the following NOWAIT statement for seat locking, return result sets 2 and 3, or return an error.

SELECT * FROM seats WHERE seat_no IN (2 and 3) AND booked = 'NO'FOR UPDATE NOWAIT

If an error is returned, it is as follows:

ERROR 3572 (HY000): Do not wait for lock.

If the two seats are successfully locked, query the status of the locking system with the following statement:

SELECT thread_id, object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks +-+ | thread_id | object_name | lock_type | lock_mode | lock_data | lock_status | +-+- -+ | 43 | seats | TABLE | IX | NULL | GRANTED | | 43 | seats | RECORD | X | 2 | WAITING | 42 | seats | TABLE | IX | NULL | GRANTED | 42 | seats | RECORD | X | 2 | GRANTED | 42 | seats | RECORD | X | 3 | | GRANTED | +-- + |

SKIP LOCKED can also be easily used for random allocation of seats. For example, we only need to lock two empty seats through the following statement.

SELECT * FROM seats WHERE booked = 'NO' LIMIT 2 FOR UPDATE SKIP LOCKED

The SKIP LOCKED/NOWAIT function is only for row locks (record lock), excluding table locks (table lock) and metadata locks (metadata lock/MDL). As a result, queries with SKIP LOCKED/NOWAIT can still be blocked by table or Metabase locks. Metadata locks are created by the MySQL Server layer to protect the consistency of concurrent access to database objects, which include not only tables, but also libraries, functions, stored procedures, triggers, events, and so on. Table and row locks are locks of different granularities created within the InnoDB storage engine to ensure transaction consistency.

In addition, SKIP LOCKED/NOWAIT can be used with FOR SHARE and can be bound to a single table. For example:

SELECT seat_noFROM seats JOIN seat_rows USING (row_no) WHERE seat_no IN (2) AND seat_rows.row_no IN (12) AND booked = 'NO'FOR UPDATE OF seats SKIP LOCKEDFOR SHARE OF seat_rows NOWAIT

Part two: code implementation of SKIP LOCKED/NOWAIT in InnoDB

In InnoDB, the implementation of SKIP LOCKED/NOWAIT is as follows:

1. Add a new query mode enum select_mode {SELECT_ORDINARY = 0, / * default behaviour / SELECT_SKIP_LOCKED, / skip the row if row is locked / SELECT_NO_WAIT / return immediately if row is locked * /}

two。 Before the query starts, set the query mode ha_innobase::store_lock (): / * Set select mode for SKIP LOCKED / NO_WAIT * / switch (lock_type) {case TL_READ_SHARED_SKIP_LOCKED: case TL_WRITE_SKIP_LOCKED: masking prebuild-> select_mode = SELECT_SKIP_LOCKED; break; case TL_READ_SHARED_NO_WAIT: case TL_WRITE_NO_WAIT: masking prebuild-> select_mode = SELECT_NO_WAIT; break Default: masked prebuild-> select_mode = SELECT_ORDINARY; break;}

3. In the lock function, if the record is locked, the corresponding processing is performed for different query modes: lock_rec_lock_slow (): if (wait_for! = NULL) {switch (sel_mode) {case SELECT_SKIP_LOCKED: err = DB_SKIP_LOCKED; break; case SELECT_NO_WAIT: err = DB_LOCK_NOWAIT; break

4. The locked result is processed in the query: row_search_mvcc (): case DB_SKIP_LOCKED: goto next_rec; handles DB_LOCK_NOWAIT by rolling back the current statement (statement), as shown in the function row_mysql_handle_errors ().

5. The processing of the secondary index (secondary index) in InnoDB, there are two cases of locking records in the table. The first is that the query uses a clustered index (cluster index), so the records of the clustered index are locked directly; in the second, if the query uses a secondary index, then first lock the records of the secondary index, and then find the corresponding clustered index records to lock according to the records of the secondary index. Therefore, for the seat table booked in the first part, if there is a secondary index, for a record in the locking table, whether the lock is successful or not is subject to the locking of the clustered index record.

SKIP LOCKED/NOWAIT can implement the reservation scenario very efficiently, and as the original author of the InnoDB section (WL#8919: InnoDB: Implement NOWAIT and SKIP LOCKED), I look forward to sharing more usage scenarios of this feature.

On how to use the database to achieve a reservation function is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.