dbms_DBMS | 并发控制

喜欢ヅ旅行 2023-03-05 09:50 22阅读 0赞

dbms

Management of concurrent transaction execution is known as **“Concurrency Control”**. Transaction management in DBMS handles all transaction, to ensure serializability and isolation of transaction. DBMS implement concurrency control technique so that the consistency and integrity of the database can be hold.

并发事务执行的管理称为**“并发控制”** 。 DBMS中的事务管理处理所有事务，以确保事务的可串行性和隔离性。 DBMS实施并发控制技术，以便可以保持数据库的一致性和完整性。

The processes of managing simultaneously operation such that update, create, insert, delete on the database without having an interface with one another, known as concurrency control. It can be performed by various methods such as locking technique, time stamp technique, validation based technique.

同时管理操作的过程，例如在数据库上相互更新，创建，插入，删除而无需相互接口，这称为并发控制。 它可以通过各种方法执行，例如锁定技术，时间戳技术，基于验证的技术。

### 并发控制的锁定技术 **(**Locking Technique for Concurrency Control**)** ###

Locking is a procedure which controls concurrent access of the data. If one transaction is accessing the database, a lock may be denied access to other transaction to prevent the incorrect result. Locking technique is one of the most widely used mechanisms to ensure serializability. The principle of locking technique is that a transaction must obtain read or write lock in the database before it can perform a read or write operation.

锁定是控制并发访问数据的过程。 如果一个事务正在访问数据库，则可以拒绝锁访问其他事务，以防止产生错误的结果。 锁定技术是确保可序列化性的最广泛使用的机制之一。 锁定技术的原理是，事务必须先获得数据库中的读取或写入锁定，然后它才能执行读取或写入操作。

There are some basic rules for locking technique are as following:

锁定技术有一些基本规则，如下所示：

*  If a transaction has read lock on the data item, it can read the item but can’t update it.
    
    如果事务对数据项具有读取锁定，则它可以读取该项目但不能更新它。
 *  If a transaction has read lock on the data item, other transaction can read on data item but can’t write on the data item.
    
    如果事务对数据项具有读取锁定，则其他事务可以在数据项上读取，但不能在数据项上写入。
 *  If a transaction has to write lock on the data item, it can both read and update data item.
    
    如果事务必须在数据项上写锁，则它可以读取和更新数据项。
 *  If a transaction has to write lock on data item then the other transaction neither read nor write on the data item.
    
    如果一个事务必须在数据项上写锁，则另一个事务既不在该数据项上读写。

### 锁定方式 **(**Mode of Locking**)** ###

There are basically two types of modes in which a data item may be locked:

基本上有两种模式可以锁定数据项：

1.  Shared Lock
    
    共享锁
2.  Exclusive Lock
    
    排他锁

### 1)共享锁 **(**1) Shared Lock**)** ###

If a transaction **T** has a shared lock on the data item **q**, then **T** can read but can’t write on **q** data item. It is denoted by (**S**).

如果事务**T**在数据项**q**上具有共享锁，则**T**可以在**q**数据项上读取但不能写入。 用( **S** )表示。

### 2)排他锁 **(**2) Exclusive Lock**)** ###

If transaction **T** has an exclusive lock on data item **q**, then **T** can read and write both on the data item. It is denoted by (**X**).

如果事务**T**对数据项**q**具有排他锁，则**T**可以在数据项上进行读写操作。 用( **X** )表示。

**In order to access the data item, these conditions should be followed:**

**为了访问数据项，应遵循以下条件：**

*  Transaction T must first lock it. If the data item is already locked by another transaction which is not comparable then T must wait until have been released & unlock.
    
    事务T必须首先将其锁定。 如果数据项已经被另一个不可比较的事务锁定，则T必须等待直到被释放并解锁。
 *  The transaction must hold a lock on data item when it is accessed.
    
    事务在访问时必须对数据项保持锁定。
 *  After that unlock it.
    
    之后，将其解锁。

**A matrix given below shows the comparability between two lock modes:**

**下面给出的矩阵显示了两种锁定模式之间的可比性：**

<table> 
 <tbody> 
  <tr> 
   <th></th> 
   <th>Shared (S)</th> 
   <th>Exclusive(X)</th> 
  </tr> 
  <tr> 
   <td>S</td> 
   <td>T</td> 
   <td>T</td> 
  </tr> 
  <tr> 
   <td>X</td> 
   <td>F</td> 
   <td>F</td> 
  </tr> 
 </tbody> 
</table>

**Example:**

**例：**

Consider the following 2 transaction **T(read(a), read(b))**.

考虑以下2个事务**T(read(a)，read(b))** 。

<table> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td>T2</td> 
  </tr> 
  <tr> 
   <td> <pre><code>read(a)
read(b)
if a=0
then b=b+1
write(b)
</code>
</pre></td> 
   <td> </td> 
  </tr> 
  <tr> 
   <td> </td> 
   <td> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> T1 </td> 
   <td> T2 </td> 
  </tr> 
  <tr> 
   <td><pre><code>read(a)
read(b)
if a=0
then b=b+1
write(b)
</code></pre></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

**Find lock and unlock instruction of the transaction T1 & T2. Then observe the locking protocol? The execution of transaction is deadlock?**

**查找交易T1和T2的锁定和解锁指令。** **然后遵守锁定协议？** **交易的执行是否陷入僵局？**

**Solution:**

**解：**

**Lock and unlock instruction:**

**锁定和解锁说明：**

<table> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td>T2</td> 
  </tr> 
  <tr> 
   <td> <pre><code>lock S(a)
read(a)
lock X(b)
read(b)
if a=0
then b=b+1
write(b)
unlock(b)
unlock(a)
lock S(b)
read(b)
lock (a)
read(a)
if b=0
then a=a+1
write(a)
unlock(a)
unlock(b)
</code>
</pre></td> 
   <td> </td> 
  </tr> 
 </tbody> 
</table>

**Yes, transaction has deadlock.**

**是的，交易陷入僵局。**

> 翻译自: [https://www.includehelp.com/dbms/concurrency-control.aspx][https_www.includehelp.com_dbms_concurrency-control.aspx]

dbms

[https_www.includehelp.com_dbms_concurrency-control.aspx]: https://www.includehelp.com/dbms/concurrency-control.aspx