Home Database Mysql Tutorial Oracle 数据库针对表主键列并发导致行级锁简单演示

Oracle 数据库针对表主键列并发导致行级锁简单演示

Jun 07, 2016 pm 05:56 PM
concurrent

本文简单演示针对表主键并发导致的行级锁,锁的产生是因为并发。没有并发,就没有锁。并发的产生是因为系统需要,系统需要是因为用户需要,感兴趣的你可以参考下哈,希望可以帮助到你

本文内容
•软件环境
•简单演示 Oracle 数据库并发导致行级锁
本文简单演示针对表主键并发导致的行级锁。并发是两个以上的用户对同样的数据进行修改(包括插入、删除和修改)。锁的产生是因为并发。没有并发,就没有锁。并发的产生是因为系统需要,系统需要是因为用户需要。

软件环境
--------------------------------------------------------------------------------
•Windows 2003 Server
•Oracle 11g Release 1 (11.1)
简单演示 Oracle 数据库并发导致行级锁
首先,打开一个会话 session 1,执行如下操作:
代码如下:
SQL> select distinct sid from V$mystat;

SID
----------
118
SQL> create table t (x int primary key);
表已创建。
SQL> insert into t values(1);
已创建 1 行。
SQL> update t set x=10 where x=1;
已更新 1 行。
SQL>

先查看本次会话的 SID,然后创建一个表 t,只有一个名为 x 的字段,且该字段为主键,插入一条数据,并更新该数据。

接下来,打开另一个会话 session 2:
代码如下:
SQL> select distinct sid from V$mystat;

SID
----------
137
SQL> update t set x=10 where x=1;

此时,该会话被“卡”在这里不动。只是光标一直在闪……因为,session 2 被 session 1 阻塞。
现在,查看锁定视图 V$LOCK。
代码如下:
SQL> select sid,type,id1,id2,lmode,request,block
2 from v$lock where sid in (118,137)
3 order by sid;


已选择6行。
SQL>
说明:“TYPE”列表示锁的类型;“LMODE”列表示锁的模式;“ID1”和“ID2”列是锁的相关信息。“REQUEST”列是正在什么锁。

SID=118 是第一个会话,SID=137 是第二个会话。第三行,第一个会话 BLOCK=1 表示这个会话正在阻塞其他会话,LMODE=6 表示锁的模式,即行级排他锁。第六行,第二个会话 REQUEST=6 表示当前会话正在等待一个 LMODE=6 的锁。注意,第三行和第六行的 ID1 和 ID2 列完全相同。因为它们指向统一资源,只不过一个是资源的拥有者(SID=118),一个是资源的等待者(SID=137)。

通过这个视图,很容易发现所在——故障定位(trouble shooting)。会话 2 之所以被“卡”住,是因为会话 1 还没提交,而在这张表上,又恰好有要求列值唯一性约束。
通过 SID 号,查看视图 V$SESSION 就可以确定用户信息。
代码如下:
SQL> select machine from v$session where sid in (118,137);

MACHINE
----------------------------------------------------------------
NUODE\LN
NUODE\LN
SQL>

因为,两个会话是同一台机器,所以名字一样。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How can concurrency and multithreading of Java functions improve performance? How can concurrency and multithreading of Java functions improve performance? Apr 26, 2024 pm 04:15 PM

Concurrency and multithreading techniques using Java functions can improve application performance, including the following steps: Understand concurrency and multithreading concepts. Leverage Java's concurrency and multi-threading libraries such as ExecutorService and Callable. Practice cases such as multi-threaded matrix multiplication to greatly shorten execution time. Enjoy the advantages of increased application response speed and optimized processing efficiency brought by concurrency and multi-threading.

Application of concurrency and coroutines in Golang API design Application of concurrency and coroutines in Golang API design May 07, 2024 pm 06:51 PM

Concurrency and coroutines are used in GoAPI design for: High-performance processing: Processing multiple requests simultaneously to improve performance. Asynchronous processing: Use coroutines to process tasks (such as sending emails) asynchronously, releasing the main thread. Stream processing: Use coroutines to efficiently process data streams (such as database reads).

A guide to unit testing Go concurrent functions A guide to unit testing Go concurrent functions May 03, 2024 am 10:54 AM

Unit testing concurrent functions is critical as this helps ensure their correct behavior in a concurrent environment. Fundamental principles such as mutual exclusion, synchronization, and isolation must be considered when testing concurrent functions. Concurrent functions can be unit tested by simulating, testing race conditions, and verifying results.

Solving concurrency issues in PHP multi-threaded functions Solving concurrency issues in PHP multi-threaded functions May 01, 2024 pm 09:45 PM

Concurrency issues in PHP multi-threaded functions can be solved by using synchronization tools (such as mutex locks) to manage multi-threaded access to shared resources. Use functions that support mutual exclusion options to ensure that the function is not called again while another thread is executing. Wrap non-reentrant functions in synchronized blocks to protect function calls.

What are the commonly used concurrency tools in Java function libraries? What are the commonly used concurrency tools in Java function libraries? Apr 30, 2024 pm 01:39 PM

The Java concurrency library provides a variety of tools, including: Thread pool: used to manage threads and improve efficiency. Lock: used to synchronize access to shared resources. Barrier: Used to wait for all threads to reach a specified point. Atomic operations: indivisible units, ensuring thread safety. Concurrent queue: A thread-safe queue that allows multiple threads to operate simultaneously.

How does Java database connection handle transactions and concurrency? How does Java database connection handle transactions and concurrency? Apr 16, 2024 am 11:42 AM

Transactions ensure database data integrity, including atomicity, consistency, isolation, and durability. JDBC uses the Connection interface to provide transaction control (setAutoCommit, commit, rollback). Concurrency control mechanisms coordinate concurrent operations, using locks or optimistic/pessimistic concurrency control to achieve transaction isolation to prevent data inconsistencies.

How to use atomic classes in Java function concurrency and multi-threading? How to use atomic classes in Java function concurrency and multi-threading? Apr 28, 2024 pm 04:12 PM

Atomic classes are thread-safe classes in Java that provide uninterruptible operations and are crucial for ensuring data integrity in concurrent environments. Java provides the following atomic classes: AtomicIntegerAtomicLongAtomicReferenceAtomicBoolean These classes provide methods for getting, setting, and comparing values ​​to ensure that the operation is atomic and will not be interrupted by threads. Atomic classes are useful when working with shared data and preventing data corruption, such as maintaining concurrent access to a shared counter.

In-depth understanding of the functions and features of Go language In-depth understanding of the functions and features of Go language Mar 21, 2024 pm 05:42 PM

Functions and features of Go language Go language, also known as Golang, is an open source programming language developed by Google. It was originally designed to improve programming efficiency and maintainability. Since its birth, Go language has shown its unique charm in the field of programming and has received widespread attention and recognition. This article will delve into the functions and features of the Go language and demonstrate its power through specific code examples. Native concurrency support The Go language inherently supports concurrent programming, which is implemented through the goroutine and channel mechanisms.

See all articles