SQL Server Learning Note Series 8

First, write it in front.

Recently has been thinking about a problem, what can let us do not appear impetuous, the real calm down, the heart to feel, intentions to answer everyone's questions, intentions to help others. The reality of life, often let us appear exhausted, and then we carefully thought about useless, in fact, support we have been walking down in fact is the heart of that a hope that the future will be better, with a forced aligning higher words, perhaps that is ideal. So, some would say, what is the ideal, because the burden of life has put our so-called ideals erased. However, that is not what, now, I have been defeated, I used the moths, in exchange for today's palm holding a surplus of the remaining Ashes. Fortunately, I still do not forget that the name of the Ashes is called ideal. Homeopathy and for the same time on the things persistent and firm, towards the goal, the ideal may not be far away, so the ideal or to have, in the event of the realization of it?

If people's activities are not inspired by ideals, they will become empty and insignificant. --Czerny Snow

Two. Constraints on tables

When we create the table, in order to ensure the correctness and completeness of the data, we usually need to verify the data, that is, the table we are talking about constraints. The constraints of the tables that are commonly used include:

(1) The constraint of the primary key.

(2) the only constraint.

(3) non-null constraints.

(4) Check the constraints.

(5) Constraints on foreign keys

Here we first create a class table and set up the corresponding constraints:

The class ID and class name respectively define the primary KEY constraint and the unique constraint, and the PRIMARY KEY constraint guarantees that only one column exists in the table, and the unique constraint ensures that the column field values cannot be duplicated and must be unique.

We then define a student table, which also establishes the appropriate constraints.

By building a table we can see that the student's number (STUDID) is defined as identity, meaning that the field grows from 100 and grows at 1 each time. where sex (gender) exists a check constraint that ensures that data inserted into the data table exists only (' male ', ' female ') in two ways, avoiding other dirty data insertions.

Again, one more important constraint-the FOREIGN KEY constraint. A FOREIGN key constraint, which indicates that the data in one table relies on another table, which has an association relationship. As with the two tables above, a student table can define a class ID to distinguish which class the student belongs to. Then you can define this:

In other words, only the class number (CLASSID) that exists in the class table can appear in the student table. Avoid the students can not find the situation of the class. In this way, the class table and student table through the foreign key ClassID, established a relationship. If we want to delete the class table, the system will error, because there is a foreign key association in the student Table (Students). Therefore, you must delete the student table before deleting the class table.

Three. transactions (Transaction)

1. The so-called transaction, which is a series of operations performed as a single unit of work. That is, it takes a lot of steps to complete something, and after all the steps are done, it's done, and any step in it is a problem, and it's not going to happen. The sequence of steps described here, as a whole, is a matter.

(1) Implicit transactions

Implicit transactions are hidden, that is, each time a DML operation is performed, it is committed directly to the database save. Let's look at the data for the freight company table first:

1 SELECT * from sales. Shippers

For example, our delete operation, for the Freight company table (sales.shippers), we delete the company with the number ShipperID 6th:

1  DELETE from Sales.shippers WHERE shipperid=6

After the deletion, we compare the previous record, found that the 6th freight company did delete, this is an implicit transaction, the default has been submitted, the impact is permanent.

(2) Show transactions

Displaying a transaction is a clear indication of the starting boundary of the transaction. The results of transactions can be handled by rolling back operations (rollback) and commits. The benefit of displaying transactions is to ensure that the integrity of the execution, such as bank transfers, succeeds, or fails, and takes a rollback operation back to its original state.

Defines the boundary of a transaction, where the transaction begins with begin transaction--(contains additions and deletions)-commit (commit) or rollback (rollback).

Then we also take the deletion operation, delete the freight company form inside the freight company number is 5,6 company. Then we have to make sure that 5, company 6th is either completely deleted or not deleted, so we need to use the transaction. SQL is as follows:

1        BEGIN transaction;2        3         Delete from sales.shippers WHERE shipperid=4;4         Delete from sales.shippers WHERE shipperid=5;5         6         ROLLBACK;

We start a transaction, then delete the operation, finally perform the rollback operation, the database table back to the previous state, although the deletion operation, but rollback rollback, let the data back to the previous state.

If we perform a commit operation, then the data cannot be rolled back. Because it was committed to the database save.

1        BEGIN transaction;2        3         Delete from sales.shippers WHERE shipperid=4;4         Delete from sales.shippers WHERE shipperid=5;5         6         commits;

2. Properties of a transaction (ACID)

(1) atomicity. The transaction must be an atomic unit of work, which means that the operations performed in the transaction, or all of them, are not executed.

(2) consistency. That is, they are either updated or not updated. For example, if we want to change the company name of the shipping company to ABC,XYZ, we can do this in the transaction:

1        BEGIN transaction;2        3         update  sales.shippers SET companyname= ' abc ' WHERE shipperid=1;4         update  sales.shippers SET companyname= ' XYZ ' WHERE shipperid=2;

Let's create a new query window to check the results of the update:

Then after we submit, we can query the result, and indeed has changed the name of 1, 2nd freight company.

1        BEGIN transaction;2        3         update  sales.shippers SET companyname= ' abc ' WHERE shipperid=1;4         update  sales.shippers SET companyname= ' XYZ ' WHERE shipperid=2;5         6         commits;

This reflects the consistency of the transaction, and only two are updated to perform other operations.

(3) Isolation. In fact, the above also reflects a kind of isolation, that is, isolation, so that we can not see how the internal transaction of a step-by-step update, only after the transaction submitted to see the unified results. Similar to the above Update 1, 2nd freight company company name, only when they are all updated, we only see the results of the update.

(4) Durability. Commits to the database to save data, is persistent to the database, and affects the permanent.

Four. Locking (lock) and blocking

A lock is a control resource acquired by a transaction that is used to protect data resources from conflicting or incompatible access to data by other transactions.

(1) Exclusive lock

own resources alone, and other transactions cannot be accessed.

(2) Shared lock

Shared resources can be accessed, and each individual enjoys data resources.

To illustrate the application of the lock, we created a new three query window with the process number 52,53,54:

In the 52 window, we create a new transaction to execute, adding a dollar to the unit price of the product number 2 in the Product table.

1 BEGIN transaction;2 3 UPDATE production.products 4 SET unitprice=unitprice+15 WHERE productid=2;

In order to update this line, the session must be given an exclusive lock in order to guarantee the completion of the update. So now we're trying to do the same row of records in window 53rd, what's the result of the query?

Exclusive and shared locks are represented here. To get information on the execution of the lock, we can make a related query in window # 54th, the dynamic management view (sys.dm_tran_locks).

1 SELECT  2  request_session_id,--Process ID 3  resource_type,     --resource type 4  resource_database_id, 5  db_ NAME (request_session_id), 6 resource_description, 7 resource_associated_entity_id, 8 Request_mode, 9 request_status10< C7/>11 from Sys.dm_tran_locks

In order to get more information about blocking links, we can then query the sys.dm_exec_connections dynamic management view in window # 54th.

1 SELECT2 session_id,3 connect_time,4 last_read,5 last_write,6 most_recent_sql_handle7 from Sys.dm_exec_connections8 WHERE session_id in (52,53);

In the execution result, we can see the link time, the last read and write time, and the number of the execution SQL.

In order to see the results more carefully, we can also find out the SQL statements executed by the system.

1 UPDATE production.products   SET unitprice=unitprice+1  WHERE productid=2;

In the query results, we can see the SQL statements executed by the system:

Sometimes we can find out which processes are blocked in order to exclude a block, then we can remove the blocking by the corresponding action. This can be queried using sql:

1 SELECT  2 session_id, 3 blocking_session_id, 4 command, 5 sql_handle, 6 database_id, 7 Wait_type, 8 wait_time, 9 wait _resource10 from  sys.dm_exec_requests11 WHERE blocking_session_id>0;

The query results show that process number 53rd is blocked by process 52nd, and can see the waiting time as well as the waiting resources.

So the number 52nd process has been waiting, in order for the process to be released, not always waiting, then we can set the timeout, more than the response set time, the end of the session. Setting timeouts can be set up like this:

1     SET lock_timeout 5000;2     3     SELECT * from  production.products 4     WHERE productid=2;

Once set, the request time is exceeded, and the call ends.

where set (set locktimeout-1) is infinite wait. The default is-1, and there is another usage of end-of-conversation kill. The corresponding process number can be terminated directly.

For example, we ended the process of 52nd, (Kiil 52).

1 KILL 52;

Learn here today, next time you learn the isolation level created by the lock.

SQL Server Learning Note Series 8