SQL Server and MySQL lock hint comparison

SQL Server Chapter


Tips in SQL Server

SQL Server hint (Hints) is the specified mandatory option or policy that is executed by the SQL Server query processor for SELECT, INSERT, UPDATE, or DELETE statements. The hint overrides any execution plan that the query optimizer might choose for the query.

It is divided into:

• Join hint (join Hints)

• Query Hint (Hints)

• Table hints (table Hints)

What is the hint (Hints) and the classification of the hint can be described in:
Http://www.cnblogs.com/downmoon/archive/2012/04/13/2445694.html


table hints in SQL Server

Table hints Override the default behavior of the query optimizer during Data manipulation language (DML) statement execution by specifying a locking method, one or more indexes, query processing operations such as table scan or index lookup, or other options. A table hint is specified in the FROM clause of a DML statement, affecting only the table or view referenced in the clause.

What are the table hints, please refer to:
Https://msdn.microsoft.com/zh-cn/library/ms187373.aspx


table-level locking hints supported by SQL Server

HOLDLOCKHold a shared lock until the entire transaction is complete and should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level
NOLOCKStatement execution does not emit a shared lock, allowing dirty reads, equal to the READ UNCOMMITTED transaction isolation LEVEL
PaglockLock with multiple pages where a table lock is used
READPASTLet SQL Server skip any locking lines, perform transactions, apply to READ UNCOMMITTED transaction isolation LEVEL only skip RID lock, skip page, zone and table lock
RowlockForce row locks to be used
TablockxEnforces exclusive table-level locks, which prevent any other transactions from using this table during a transaction
UplockForce updates to be used when reading tables without sharing locks

The two most common locking hints are with (NOLOCK) and with (Updlock,holdlock).

With (NOLOCK)Tell the server not to request any locks:

SELECT col1,col2 from Tbl1 with (NOLOCK);

This is very efficient because the extra overhead of locking is avoided. The side effect is that you will read uncommitted data from another transaction.

With (Updlock,holdlock) is used to avoid lock escalation and deadlocks. Suppose I read data from a table to a temporary table, do some work, and then update the source table again. When there is no lock prompt, the initial read requests a shared read lock, which is then upgraded for write. At the same time, other transactions are requesting a shared lock, and this upgrade causes at least a blockage, possibly a deadlock. To avoid it, apply and hold a write lock as soon as possible in the transaction. For example:

CREATE TABLE #temp ... INSERT into #temp ... SELECT Col1, Col2, Col3from tbl1 with (Updlock,holdlock)

Transaction ISOLATION LEVEL vs. Lock hold Time

The length of time the lock is held is the length of time required to protect resources at the requested level.

The hold time of a shared lock used to protect read operations depends on the transaction isolation level. When using the default transaction isolation level of Read COMMITTED, the shared lock is only controlled during the time the page is read. In the scan, the lock is released until the lock is acquired on the next page within the scan. If you specify HOLDLOCK hint or set the transaction isolation level to repeatable READ or SERIALIZABLE, the lock is not released until the end of the transaction.

The default transaction isolation level for SQL Server is read COMMITTED, but we need to serializable. You can use the following hints to let SQL Server ignore using the Read Committed isolation level and use Serializable instead until the transaction is complete:

SELECT col1,col2,col3from tbl1 with (UPDLOCK, HOLDLOCK)--HOLDLOCK are equivalent to SERIALIZABLE

UPDLOCK:Select and insert are not blocked in the transaction. It blocks any update or delete rows for the transaction query.
HOLDLOCK:is equivalent to seralizable, and allows select, but blocks update and delete rows for the transaction query, and blocks any inserts for the scope of the transaction query.


SQL Server transaction ISOLATION levelSee:
http://ultrasql.blog.51cto.com/9591438/1839116


isolation level for SQL Server row versioningSee:
Http://www.cnblogs.com/xwdreamer/archive/2012/07/30/2615357.html



MySQL Chapter


InnoDB Lock


row-level locking:

Shared (S) Locks:It allows a transaction to read a row, and if a transaction of a session obtains the lock, a transaction with no other session can apply an X lock on the same line.
Exclusive (X) Locks:It allows one transaction to update or delete a row. If a session's transaction obtains this lock, a transaction with no other session can apply an S or X lock on the same line.

Official definition:
If the transaction T1 holds the S lock on the R line, then the transaction T2 request is locked in the R row as follows:

• Transaction T2 Request S lock will be granted immediately. As a result, both T1 and T2 hold S locks on the R line.

• Transaction T2 Request X lock cannot be granted immediately.

If the transaction T1 holds an X lock on the R line, then the request of the transaction T2 cannot be granted immediately on any type of lock on the R line. The transaction T2 only waits for the transaction T1 to release the lock on the R row.

Table-level locking:

Intent Lock (Intention Locks):
The INNODB supports multi-granularity locking, allowing row and table locks to coexist. The idea behind an intent lock is that for a transaction, it indicates what type of lock (s or x) will be applied to it after a row of the table. There are two types of intent locks used in InnoDB:

• Intention Shared (IS): allows a request to lock a rowset in s or is mode.

• Intention Exclusive (IX): allows a request to display a locked rowset in X, S, IX, or is mode.

For example, the following will be referred to select ... Lock in SHARE mode sets an IS lock, SELECT ... For update sets an IX lock.

For more InnoDB lock details, see:
Http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html


InnoDB Lock Read (Locking Reads)


References from official documents:
“
If you query data and then insert or update related data within the same transaction, the regular SELECT statement does no T give enough protection. Other transactions can update or delete the same rows just queried. InnoDB supports-types of locking reads that offer extra safety:

. SELECT ... Lock in SHARE mode sets a GKFX mode lock on any rows that is read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of the these rows were changed by another transaction that have not yet committed, your query waits until that transacti On ends and then uses the latest values.

. For index records the search encounters, SELECT ... For UPDATE locks the rows and any associated to index entries, the same as if you issued a UPDATE statement for those rows. Other transactions is blocked from updating those rows, from doing SELECT ... LOCK in SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (old versions of a record cannot is locked; they is reconstructed by applying undo logs in an in-memory copy of the Recor D.)

These clauses is primarily useful when dealing with tree-structured or graph-structured data, either in a single table or Split across multiple tables. You traverse edges or tree branches from one place to another, while reserving the right to come back and change any of th ESE "pointer" values.

All locks set by-LOCK in SHARE MODE and for UPDATE queries be released when the transaction was committed or rolled back.

Note:
Locking of rows for update using SELECT for update only applies when autocommit is disabled (either by beginning Transacti On with the START TRANSACTION or by setting Autocommit to 0. If autocommit is enabled, the rows matching the specification be not locked.
”

The above description mainly wants you to know two things:
1.
Select: From.. Where: For UPDATE
Select: From.. Where: LOCK in SHARE MODE
Both of these lock reads are used for select queries.

2.
LOCK in SHARE MODELocks the entire table in the IX mode and selects the rowset in S mode. Sets a shared-mode lock on the read line. This shared lock allows the other session to read the data but does not allow it to be modified. The line reads the latest data, and if he is not committed by other transactions, the read lock will be blocked until the end of the transaction.
For UPDATELocks the entire table in the IS mode, and selects the rowset in x mode. Sets an exclusive lock on the read line. Prevents other sessions from reading or writing row data.

Let's take a closer look at the following examples:

mysql1 > select * from orders where id = 21548 for  update;  #There  is no transaction in this so it immedietly  releases the lock.mysql2 > update orders set order_value =  1000 where id = 21548; #Works  because there is no lock  Applied in connection1.mysql1 > start transaction;mysql1 > select  * from custom_orders where id = 21548 FOR UPDATE; #acquired   Ix lock on table and x lock on row with id 21548mysql2  > select * from orders where id = 21548;  #worksmysql2  > update orders set order_value = 1000 where id =  21549;  #worksmysql2  > select * from orders where id = 21548 for update; # waiting for lock which is acquired by connection1 on row  with id 21548 mysql1 > start transaction;mysql1 > select *  from orders where id = 21548 FOR UPDATE; #acquired  ix lock  on table and X lock on row with id 21548mysql2 >  start transactionmysql2 > select * from orders where id  = 21548 for update;  #waiting #waiting for lock which is acquired  by connection1 on row with id 21548 mysql1 > start  transaction;mysql1 > select * from orders where id = 21548  lock in share mode; #acquired  IS lock on table and S lock on row  With id 21548 so can ' t update this row by different  connectionmysql2 > update orders set order_value = 1000 where  id = 21549;     #works  because row with id 21549 is  not locked.mysql2 > update orders set order_value = 1000  where id = 21548; #waiting  for lock which is acquired by  connection1 on row with id 21548 mysql1 > start  transaction;mysql1 > select * from orders where id = 21548  LOCK IN SHARE MODE; #acquired  IS lock on table and S  Lock on row with id&nBsp;21548 so can ' t update this row by different connectionmysql2  > select * from custom_orders where id = 21548 for update ; #waiting  for lock which is acquired by connection1 on row  with id 21548  #This  is asking for x lock but can ' T be  given as connection1 has s lock on the same row.

MySQL must keep the lock on each line it updates until the transaction commits, so if the transaction fails or cancels it will roll back all at once.

MYSQL1 > Start transaction;mysql1 > Update orders Set order_value = n where id = 21548; #As This is happening in  Transaction, MySQL automatically applies lock on the concerned ROW.MYSQL2 > select * FROM orders where id = 21548 lock In SHARE MODE, #waiting for the lock acquired by Connection1 on the same row.

MVCC: Snapshot read (Snapshot read) vs current Read

The MySQL InnoDB storage engine, which implements the Concurrency Control Protocol--MVCC (Multi-version Concurrency Control) based on multiple versions (note: Compared to MVCC, is lock-based concurrency control, lock-based Concurrency Control). MVCC The greatest benefits, I believe it is also familiar: Read no lock, read and write no conflict. In an OLTP application that reads and writes less, read-write conflicts are important, greatly increasing the concurrency of the system, which is why at this stage, almost all RDBMS support the MVCC.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read. Snapshot reads, read the visible version of the record (possibly a historical version), without locking. The current read, read is the latest version of the record, and the current read returned records, will be added to the lock, to ensure that other transactions will no longer concurrently modify this record.

In a system that supports MVCC concurrency control, which read operations are snapshot reads? Which operations are currently read? Take MySQL InnoDB as an example:

Snapshot read: a simple select operation, which belongs to the snapshot read, without locking. (Of course, there are exceptions, which are analyzed below)

• SELECT * from table where?;

Current read: Special read operation, insert/update/delete operation, belongs to the current read, need to lock.

• SELECT * FROM table where? lock in Share mode;

• SELECT * FROM table where? for update;

• Insert into table values (...);

• Update table set? Where?;

• Delete from table where?;

All of the above statements belong to the current read, reading the latest version of the record. Also, after reading, it is necessary to ensure that other concurrent transactions cannot modify the current record and lock the read record. In addition to the first statement, the read record plus S lock (shared lock), the other operation, plus X lock (exclusive lock).

More MVCC snapshot Read and current read, see:
http://hedengcheng.com/?p=771

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1839484

SQL Server and MySQL lock hint comparison