Previous 1 2 3 4 Next 5, Lock Table Despite the transaction is a very good way to maintain database integrity, but because of its exclusive, sometimes it will affect the performance of the database, especially in large applications. In the system. Since the database will be locked in the process of transaction, other user requests can only be temporarily waiting until the transaction ends. If a database system only has a few users to use, the impact of transactions will not be a big problem; but there is a matter of thousands of users to access a database system, such as accessing an e-commerce website, will result A more serious response delay. In fact, in some cases we can get better performance by locking the table. The following example uses a method of locking the table to complete the function of the transaction in the previous example.
Lock Table Inventory WriteSelect Quantity From InventorywhereItem = 'Book'; ... Update Inventory Set Quantity = 11whereItem = 'Book'; Unlock Tables Here, use a SELECT statement to remove the initial data, with some calculations, update the new value with the UPDATE statement In the table. Lock Table statements containing WRITE keywords ensure that there will be no other access to Inventory before the unlock tables command is executed. 6. The method of using foreign key locks can maintain data integrity, but it does not guarantee the correlation between data. At this time we can use foreign keys. For example, foreign keys ensure that each sales record points to a customer existing. Here, the foreign key can map the Customerid in the CustomerInfo table to the Customerid in the SalesInfo table, and any records without legal Customerid will not be updated or inserted into SalesInfo.
CREATE TABLE customerinfo (CustomerID INT NOT NULL, PRIMARY KEY (CustomerID)) TYPE = INNODB; CREATE TABLE salesinfo (SalesID INT NOT NULL, CustomerID INT NOT NULL, PRIMARY KEY (CustomerID, SalesID), FOREIGN KEY (CustomerID) REFERENCES customerinfo (CustomerID ) On deletecascade; type = innoDB;
