byLessHttp://blog.csdn.net/shendl
The design of relational database table structure has the following two design techniques:
The physical primary key, as the associated foreign key relational database, is composed of multiple data tables. The structure of each data table is the same, and there may be associations between different tables. The relationship between the tables is the reason why the relational database is named.
A table is composed of multiple fields. There may be more than one field suitable as the primary key. The primary key field is the field in the table where there is no duplicate data for each row.
The primary key can be divided into two types: physical primary key and logical primary key.
The table for each database uses the self-growing ID field as the physical primary key.
A foreign key association between multiple tables that associates the physical primary key of another table, which is the ID field associated with other tables.
A logical primary key is a field that is not duplicated except for the ID field. When we design a foreign key association for a database, we use a physical primary key instead of a logical primary key.
This is because the physical primary key is definitely the primary key, because it is self-increasing. (for relational databases that do not support the self-increment field, you can use the UUID to guarantee the uniqueness of the physical primary key)
The logical primary key, however, may become a repeatable field as the business progresses. Once this happens, the foreign key relationship of the relational database is destroyed. For example, you might think that the name is a logical primary key, but you might find someone with the same names later, then this field is no longer a primary key. If you previously used this field as a foreign key, then the data returned by the query would not correspond to one by one.
This is why the physical primary key is used, and the physical primary key is used as the reason for the associated foreign key.
Using optimistic locks to update records that depend on previous states consider a scenario where a database is used:
The takeover of a task order: multiple operations personnel take over a task from a task pool. Only the first operator who gets the task can successfully take over the task, and subsequent operations personnel must fail to take over the operation.
At this point, you need a locking mechanism for database records. Only the first transaction can update a record.
The database can use pessimistic and optimistic locks to lock database records.
Pessimistic locks are implemented by the following SQL statement:
SELECT * from T for UPDATE
This statement returns the most recent data after the other modified transaction commits.
Once this statement is executed, the records are locked and cannot be modified by other SQL transactions. Until this transaction is committed.
Optimistic locking is an application implementation, not a mechanism for database implementation. Optimistic lock, for the database, is not locked. A transaction can select data that has already been committed by another transaction. When you update data, the database guarantees that updates to multiple transactions are atomic.
Pessimistic locks cause transactions to wait for other transactions to complete. Optimistic locks, which only wait for the completion of the UPDATE statement for other transactions, do not wait for the entire transaction to complete and are therefore more efficient.
Ways to implement optimistic locking:
Add a version field to the database table. Version is a number-type field, with each update adding 1. Each update is checked to see if the version field is the same as the value of the current transaction. If the version field is different, it indicates that another transaction has updated the record after querying the data, causing the update to fail. The app must reload the latest data and then update the data again.
If optimistic locking is used, then if version in the database is the same as in the app, then the versions value is updated with version+1.
The SQL statements are as follows:
Update studentversion set ver=?, Name=? where id=? and ver=?
Update independent State records without using locks
Consider a scenario in which a database is used:
The status of the virtual machine needs to be updated. Multiple transactions may update the state of the virtual machine at the same time as start or stop. Updates of this state are irrelevant to the state of the previous phase, so there is no need to lock the record. Update directly. You do not need to use pessimistic or optimistic locks at this time.
If the table adds a version field, ignore the comparison and update of the version field directly.
The SQL statements are as follows:
Update studentversion set name=? where id=?
Summarize
When you design a table for a relational database, you need to add an ID field (self-increment field, or UUID field) and a version field (numeric type) to the table. The ID field is used as a physical primary key to guarantee the non-repeatability of records and to be used as a foreign key association.
The version field is used to implement optimistic locking, providing better performance than pessimistic locks. Especially for the UI display and possibly the concurrent update of the data, more need to use optimistic locking to improve database access performance.
For tasks that are automatically updated in the background, you can use an optimistic lock implementation. But it needs to be automatically backed up when a conflict occurs. You can also use pessimistic locks to queue transactions on the database to resolve update conflict issues.
For scenarios that do not care about the relationship between recorded states, you can update the records directly, ignoring the detection and update of the version field.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Two design techniques of relational database table structure
