search
HomeDatabaseMysql TutorialOracle 丢失更新问题的解决方案

丢失更新是数据中一个比较常见的经典问题,在做项目时我们有时可能会没有注意到这个问题,但这个问题相当重要,有时会带来比较严

丢失更新是数据中一个比较常见的经典问题,在做项目时我们有时可能会没有注意到这个问题,但这个问题相当重要,有时会带来比较严重的结果。下面我们就来讨论下这个丢失更新。

一、什么是丢失更新:

用一个操作过程来说明:

(1) 会话Session1 中的一个事务获取(查询)一行数据,并显示给一个用户User1。
(2) 会话Session2 中的另一个事务也获取这一行,但是将数据显示给另一个用户User2。
(3) User1 使用应用修改了这一行,让应用更新数据库并提交。会话Session1 的事务执行完毕。
(4) User2 也修改这一行,让应用更新数据库并提交。会话Session2 的事务执行完毕。

这个过程就叫做“丢失更新”,因为第(3)步做的操作会全部丢失(被第4步操作覆盖),最终数据库只会保存第(4)步的更新结果。这个情况在有些系统中可能不会有影响,但在有些系统中可能就影响很大了,举个简单的例子:

财务系统加工资,若公司本次调薪决定给员工张三加1k人民币,财务部两名操作人员A和B,过程情况若是这样的:

1)A操作员在应用系统的页面上查询出张三的薪水信息,然后选择薪水记录进行修改,打开修改页面但A突然有事离开了,页面放在那没有做任何的提交。

2)这时候B操作员同样在应用中查询出张三的薪水信息,然后选择薪水记录进行修改,录入增加薪水额1000,然后提交了。

3)这时候A操作员回来了,在自己之前打开的薪水修改页面上也录入了增加薪水额1000,然后提交了。

其实上面例子操作员A和B只要一前一后做提交,悲剧就出来了。后台修改薪水的sql:update 工资表 set salary = salary + 增加薪水额 where staff_id = ‘员工ID’。这个过程走下来后结果是:张三开心了这次涨了2k,操作员A和B都郁闷了。

二、解决思路:

基本两种思路,一种是悲观锁,另外一种是乐观锁; 简单的说就是一种假定这样的问题是高概率的,最好一开始就锁住,免得更新老是失败;另外一种假定这样的问题是小概率的,最后一步做更新的时候再锁住,免得锁住时间太长影响其他人做有关操作。

三、解决方案1(悲观锁)

a.传统的悲观锁法(不推荐):

以上面的例子来说明,在弹出修改工资的页面初始化时(这种情况下一般会去从数据库查询出来),在这个初始化查询中使用select ...for update nowait, 通过添加for update nowait语句,将这条记录锁住,避免其他用户更新,从而保证后续的更新是在正确的状态下更新的。然后在保持这个链接的状态下,在做更新提交。当然这个有个前提就是要保持链接,就是要对链接要占用较长时间,这个在现在web系统高并发高频率下显然是不现实的。

b.现在的悲观锁法(推荐优先使用):

在修改工资这个页面做提交时先查询下,当然这个查询必须也要加锁(select ...for update nowait),有人会说,在这里做个查询确认记录是否有改变不就行了吗,是的,是要做个确认,只是你不加for update就不能保证你在查询到更新提交这段时间里这条记录没有被其他会话更新过,所以这种方式也需要在查询时锁定记录,保证在这条记录没有变化的基础上再做更新,若有变化则提示告知用户。 

四、解决方案2(乐观锁)

a.旧值条件(前镜像)法:

就是在sql更新时使用旧的状态值做条件,SQL大致如下 Update table set col1 = newcol1value, col2 = newcol2value…. where col1 = oldcol1value and col2 = oldcol2value….,在上面的例子中我们就可以把当前工资作为条件进行更新,如果这条记录已经被其他会话更新过,则本次更新了0行,这里我们应用系统一般会做个提示告知用户重新查询更新。这个取哪些旧值作为条件更新视具体系统实际情况而定。(这种方式有可能发生阻塞,如果应用其他地方使用悲观锁法长时间锁定了这条记录,,则本次会话就需要等待,所以使用这种方式时最好统一使用乐观锁法。)

b.使用版本列法(推荐优先使用):

其实这种方式是一个特殊化的前镜像法,就是不需要使用多个旧值做条件,只需要在表上加一个版本列,这一列可以是NUMBER或 DATE/TIMESTAMP列,加这列的作用就是用来记录这条数据的版本(在表设计时一般我们都会给每个表增加一些NUMBER型和DATE型的冗余字段,以便扩展使用,这些冗余字段完全可以作为版本列用),在应用程序中我们每次操作对版本列做维护即可。在更新时我们把上次版本作为条件进行更新。

c.使用校验和法(不推荐)

d.使用ORA_ROWSCN法(不推荐) 

五、结论:

个人建议:在用户并发数比较少且冲突比较严重的应用系统中选择悲观锁b方法,其他情况首先乐观锁版本列法。

linux

Statement
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
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor