SQL Server Tour--terminal nolock Some thoughts on the level three event caused by

There was one thing I remember, that year just came Ctrip soon, was arranged to write an interface, for the Penguin company to call their employees travel information, and then I rinsed to write good

, on-line, after about one months ... The DBA has reported a large number of lock timeouts in the database, and promptly sent the email to our department based on the source of SQL, indicating that the SQL read time is too long,

And the lack of nolock, affecting a large number of ticket orders warehousing, and then I took the SQL to production environment ran down, 22s ... Flower rub ... The project went live too long, the version no longer exists, cannot

Roll back ... Originally prepared to remove the interface ... Looked under the removal of the interface with the addition of nolock time difference is not much, the final decision to add Nolock, issued emergency orders ... And then optimize it, the DBA has temporarily

When doing manual unlocking, send up, the last is the loss of XXXX order ... Rated at level three events. Then is the accountability, of course, this responsibility can only have the eldest brother to undertake, out of this this caused by me

Events, I have to think, out of the matter is not all bad for me, at least this time will let me remember, think also rub, to Ctrip before simply do not pay attention to the NOLOCK, which

also include themselves without Encounter Big Data bar, also includes own limited ability, only know has lock this thing, elaborate on words will not know, and later learned that Ctrip has a rule, is a lot of business production

The select that the line writes must specify the NOLOCK, the person who understands a bit may say that NOLOCK can improve performance, if you say so, it is true, because the database lock has 96 bytes of overhead, no

Lock, there is no you see in profile accquired and released spasm, when you read my event, you may realize that performance improvement is not the most concern, the most concern is not to appear

Deadlock, lock wait ... OK, so, let's see how many locks can be specified in the database???

One: How many locks can be specified in the end

This question is interesting, we do not need to remember, as long as you install a SQL Prompt, with this artifact, you know exactly how many? Such as:

1 DROP TABLEdbo. person2 CREATE TABLEPerson (IDINT IDENTITY, NAMECHAR(4000)DEFAULT 'xxxxx')3 INSERT  intoDbo. PersonDEFAULT VALUES4 Go 6

One eye sweep down, still a lot of, but you have to notice, those so-called xxxlock is we need to pay attention to, according to the above figure, we probably put the lock divided into categories ...

Granularity Lock: Paglock, TABLOCK, Tablockx, Rowlock, NOLOCK

Mode lock: HOLDLOCK, UPDLOCK, XLOCK

Next I start with the granularity lock:

1. NOLOCK

All said that Nolock is a lock-free mode, then what is the lock-free??? To this end, you should know that if you do not add nolock, our table, data page is attached is lock, then

Down I use profile to see what the difference between the two.

　

From there, you will see that after adding Nolock, the object is appended with the Sch-s lock, which is called the "Schema Stabilization Lock", which is simply a lock attached to SQL compile, which is intended to

Prevents other connections from modifying the table structure at compile time, and this lock only conflicts with the SCH-M lock, which is compatible with other locks, what does it mean? Description Other connections locked up the record also okay my

Nolock do not deal with them, so that they may read the dirty data, but it does not matter, Ctrip's many businesses are allowed dirty data, after all, than lock wait, deadlock much stronger, and besides

NOLOCK read the other connection unmodified or uncommitted data, this probability is also relatively low, even if it is not OK, generally will not recruit visitors, guests may then brush the page, the data

Maybe it's right, right, wrong.

2.TABLOCK

This is still more in the name of righteousness, is attached to the table on the lock, that is, the table lock, very scary ... Let me give you an example of the update to see the contrast before and after.

Did you see that the X lock was attached to the object above? In this case, the other connection will not move this object, can only wait ...

3. Paglock

Look at the name you should also know, is attached to the page this level of lock, I also give an example of update.

1 BEGIN TRAN2 UPDATEDbo. PersonSETNAME='AAAAA' WHEREId=63 4 BEGIN TRAN5 UPDATEDbo. Person with(Paglock)SETNAME='bbbbb' WHEREId=4

From the above two figures, you should be able to see that the original attached to the RID above the U lock, due to the ascension of Paglock, now to attach to the page above, this is called the data page lock.

4.TABLOCKX, Rowlock

I'm not going to go into these two, Tablockx is the X lock attached directly to the table, and you can look through the select.

Rowlock, the default is Rowlock, such as the default update, you will find the rid of the additional U lock, this is the row lock.

5.UPDLOCK

This lock is still quite interesting, it is the update lock, if you select, it will show the lock spasm effect of update.

　　

6. XLOCK

Know the Updlock lock, I think xlock you should also understand ... It is the delete lock, which is an exclusive lock, and I can have the select with an exclusive lock.

7.HOLDLOCK

The last one, I didn't make it. It is said that the statement holds the lock in the entire transaction, and then I debug it with select and update.

1 SELECT *  from dbo. Person (HOLDLOCK)2UPDATEwith (HOLDLOCKSET NAME = ' bbbbb ' WHERE ID=4

Can see, holdlock whether in the Select or update, are holding a lock on the table, no mood to study, tomorrow can go home ... Leave it to everyone to observe.

Finally, I wish you a Happy new year, a happy family, I finally finished this series years ago, but also congratulate themselves.

SQL Server Tour--terminal nolock Some thoughts on the level three event caused by