一个事务里怎么去掉写锁

2002-07-18 09:37:02 167 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

jett 墨衍会员 · AI 创作全网分发关注 + 加入社区

mysql的锁和事务 mvcc实现快照读+间隙锁解决幻读阅读详情

即一旦写的操作完毕则释放写锁不等到事务提交在释放，或是写的时候就不用锁

...全文

MySQL事务和锁作者：IT王小二博客：https://itwxe.com MySQL是怎么解决并发事务所产生的问题呢？又借助了哪些锁的思想呢？这篇小二给小伙伴们继续唠一唠MySQL的那些事。一、事务是什么事务是由一组SQL语句组成的逻辑处理单元，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。通常有下面4个特性，也就是咱程序猿俗称的ACID属性。原子性(Atomicity)：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行。举个例子：情人节小二给女朋友转账520块，那么SQ. 阅读详情

jett

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

jett 2002-07-22

打赏
举报

我找到了引起循环插入/更新所引起的死锁的原因
是由于有自增字段的关系
实际的问题算是解决了，但是我还是想问我最初的那个问题能否不等到事务提交就释放写锁，我只想用到事务的回滚功能，而不想用它的锁定功能，即一种WRITE UNCOMMITTED的方式。

to CSDNM
我不太理解你所说的原则中的最后2条
UPPDATE和DELETE要考虑使用索引
使用存储过程
为什么这样就可以减少死锁，能否解释一下

jett 2002-07-22

打赏
举报

CSDNM 2002-07-22

打赏
举报

你说的WRITE UNCOMMITTED好象没有办法解决，如果有WRITE UNCOMMITTED，你的数据更改了，但还没有提交，这是另一个进程又更改了这个数据，这样当你的进程要回滚的时候，将会没有办法回滚，因为回滚是根据日志的。

如果你的应用一定要这么实现，那我只有一个建议（其实是一个想法，也许并不能说清楚）：
1、不用事务
2、在原来的事务内的每一个语句执行前保存要修改的记录的原貌。
3、自己编写回滚算法，利用保存的记录的原貌恢复。
4、也许这个回滚算法是会回滚其他进程对记录的修改，这应该是错误，但我想不到方法避免，除非你的应用就是要这样的功能。

回过头看这个回复，好象全是废话，还是发吧，欢迎批判！

CSDNM 2002-07-22

打赏
举报

UPPDATE和DELETE要考虑使用索引
UPPDATE和DELETE加的是排他锁，排他锁很耗资源，UPPDATE和DELETE速度慢容易引起堵塞，给死锁创造条件。

使用存储过程
这里说的存储过程其实还包括用户自定义函数，总的来说都是将应用集中到服务器端，减少网络I/O，提高速度，减少死锁的机会。

这两条都不是直接避免死锁的，而是通过提高速度来间接地避免死锁的。

xhfjy 2002-07-18

打赏
举报

MARK

weixy 2002-07-18

打赏
举报

3.使用绑定连接
绑定连接允许两个或多个连接共享同一个事务和锁定。绑定连接可以对同一个数据进行操作，而不会有锁定冲突。绑定连接可以从同一个应用程序内的多个连接中创建，也可以从使用不同连接的多个应用程序中创建。绑定连接使得协调多个连接上的操作更加容易。

若要加入到绑定连接中，连接必须调用 sp_getbindtoken 或 srv_getbindtoken（开放数据服务），以获得一个绑定令牌。绑定令牌是一个字符串，它唯一地标识每个绑定事务。然后绑定令牌将发送给加入到绑定连接的其它连接。其它连接通过调用 sp_bindsession，并使用从第一个连接中接收到的绑定令牌绑定到事务上。

必须从创建第一个连接的应用程序代码将绑定令牌传送到创建随后每个绑定连接的应用程序代码中。应用程序不能使用 Transact-SQL 语句或 API 函数获取由另一个进程启动的事务绑定令牌。可以用来传送绑定令牌的方法有：

如果所有连接都是从同一个应用程序进程创建而来，那么绑定令牌可以存储在全局内存中，也可以作为参数传递到函数中。

如果连接是从不同的应用程序进程创建而来，那么绑定令牌可以使用进程间通讯 (IPC)，如远程过程调用 (RPC) 或动态数据交换 (DDE)来传输。

在 Microsoft® SQL Server™ 中可以将绑定令牌存储在某个表中，该表应能够被要绑定到第一个连接的进程读取。
在一组绑定连接中，任意时刻只能有一个连接是活动的。如果一个连接正在服务器上执行一个语句，或者包含由服务器挂起的结果，那么共享同一事务空间的其它连接都不能访问该服务器，直到当前的连接完成处理或取消了当前的语句为止。如果服务器很忙，那么就会出现错误，表明事务空间正在使用，该连接应该稍后再试。

绑定连接的类型
有两种绑定连接类型：本地和分布式。

本地绑定连接
允许绑定连接共享单个服务器上的单个事务的事务空间。

分布式绑定连接
允许绑定连接在用 Microsoft 分布式事务处理协调器 (MS DTC) 提交或回滚整个事务之前，共享跨越两个或多个服务器的同一事务。

分布式绑定连接的标识不是用字符串绑定令牌，而是用分布式事务标识号。如果本地事务中涉及到绑定连接，而且该连接使用 SET REMOTE_PROC_TRANSACTIONS ON 执行远程服务器上的 RPC，则 MS DTC 将该本地绑定事务自动升级到分布式绑定事务，并且 MS DTC 会话也会启动。

weixy 2002-07-18

打赏
举报

1.ROWLOCK 使用行级锁，而不使用粒度更粗的页级锁和表级锁。
READPAST 跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集内），而不是阻塞该事务，使其等待其它事务释放在这些行上的锁。READPAST 锁提示仅适用于运行在提交读隔离级别的事务，并且只在行级锁之后读取。仅适用于 SELECT 语句。

SQL Server2000 使用锁定确保事务完整性和数据库一致性。锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据。如果不使用锁定，则数据库中的数据可能在逻辑上不正确，并且对数据的查询可能会产生意想不到的结果。
SQL Server 自动强制锁定，但可以通过了解锁定并在应用程序中自定义锁定来设计更有效的应用程序。

2. NOLOCK 不要发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于 SELECT 语句。
如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：
丢失或覆盖更新。
未确认的相关性（脏读）。
不一致的分析（非重复读）。
幻像读。

jett 2002-07-18

打赏
举报

即使我修改了程序还是会有其他情况出现死锁
比如我需要对某一个表做循环的更新
如果写锁不是用table级的
多个事务并发操作也会出现死锁

Yang_ 2002-07-18

打赏
举报

避免死锁原则里有一条
事务操作顺序要一样，即两个事务写表顺序要么都是A，B，要么都是B，A。
你不修改程序没有办法避免经常死锁的。

我觉得你应该把重点放到修改程序上！

jett 2002-07-18

打赏
举报

to Yang_(扬帆破浪) ( )
你所说的我也查到过
我是为了要解决死锁的问题
举个例子
即2个事务x,y
如果他们同时操作2张表a,b
一个写表顺序是a,b
另一个是b,a
这样就很容易出现死锁（可能还有其它的情况）
因为应用中同时操作同一条纪录的情况及少，所以我想把写锁去掉
至少不要等到事务结束才去掉
另外也不考虑用SERIALIZABLE 的transaction并发性太差了

jett 2002-07-18

打赏
举报

Yang_ 2002-07-18

打赏
举报

写的时候的排他锁是没有办法去掉的！！

以下是用各种方法调整隔离级别的方法的帮助文本：

调整事务隔离级别
隔离属性是 ACID 的四个属性之一，逻辑工作单元必须具备这四个属性才能称为事务。该属性能够使事务免受其它并发事务所执行的更新的影响。每个事务的隔离级别实际上都是可以自定义的。

Microsoft® SQL Server™ 支持 SQL-92 中定义的事务隔离级别。设置事务隔离级别虽然使程序员承担了某些完整性问题所带来的风险，但可以换取对数据更大的并发访问权。与以前的隔离级别相比，每个隔离级别都提供了更大的隔离性，但这是通过在更长的时间内占用更多限制锁换来的。事务隔离级别有：

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE
可以使用 Transact-SQL 或通过数据库 API 来设置事务隔离级别：

Transact-SQL

Transact-SQL 脚本和 DB-Library 应用程序使用 SET TRANSACTION ISOLATION LEVEL 语句。

ADO

ADO 应用程序将 Connection 对象的 IsolationLevel 属性设置为 adXactReadUncommitted、adXactReadCommitted、adXactRepeatableRead 或 adXactReadSerializable。

OLE DB

OLE DB 应用程序调用 ITransactionLocal::StartTransaction，其中 isoLevel 设置为 ISOLATIONLEVEL_READUNCOMMITTED、ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD 或 ISOLATIONLEVEL_SERIALIZABLE。

ODBC

ODBC 应用程序调用 SQLSetConnectAttr，其中 Attribute 设置为 SQL_ATTR_TXN_ISOLATION，ValuePtr 设置为 SQL_TXN_READ_UNCOMMITTED、SQL_TXN_READ_COMMITTED、SQL_TXN_REPEATABLE_READ 或 SQL_TXN_SERIALIZABLE。

Yang_ 2002-07-18

打赏
举报

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
用SET TRANSACTION ISOLATION LEVEL设置合适的隔离级别。
设成READ UNCOMMITTED，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。

以下是帮助文本：

SET TRANSACTION ISOLATION LEVEL
控制由连接发出的所有 Microsoft® SQL Server™ SELECT 语句的默认事务锁定行为。

语法
SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

参数
READ COMMITTED

指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。

READ UNCOMMITTED

执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。

REPEATABLE READ

锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。

SERIALIZABLE

在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。

注释
一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的 FROM 子句中在表级上指定优化选项。

SET TRANSACTION ISOLATION LEVEL 的设置是在执行或运行时设置，而不是在分析时设置。

示例
下例为会话设置 TRANSACTION ISOLATION LEVEL。对于每个后续 Transact-SQL 语句，SQL Server 将所有共享锁一直控制到事务结束为止。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
SELECT * FROM publishers
SELECT * FROM authors
...
COMMIT TRANSACTION

CSDNM 2002-07-18

打赏
举报

不是说了几个原则吗？

jett 2002-07-18

打赏
举报

那怎么通过程序优化解决循环插入/更新，死锁的问题呢

CSDNM 2002-07-18

打赏
举报

锁有好多是自动的!死锁的避免应该主要是优化程序.

比如你update用rowlock,但是行锁在一定数量时会自动升级为页锁,同样道理页锁也会自动升级为表锁,所以只要你的程序经常在一个事务里更新大量数据,用rowlock也会堵塞和死锁.

事务不要太大
事务里操作顺序一致
事务里不要加用户交互
UPPDATE和DELETE要考虑使用索引
使用存储过程

CSDNM 2002-07-18

打赏
举报

weixy 2002-07-18

打赏
举报

用sp_lock查看是不是ROWLOCK。

SET LOCK_TIMEOUT 1800 试试

jett 2002-07-18

打赏
举报

更新锁能解决从select 到update所引起的死锁问题
却不能解决写锁和写锁之间的死锁问题
还有我很怀疑rowlock是不是真的是行锁
我把程序中所有的select全用nolock
insert 和update全用rowlock同样会出现死锁

OpenVMS 2002-07-18

打赏
举报

使用更新锁
更新锁用在能被更新的资源上，更新锁能阻止死锁的产生。如果两个事务在一资源上要求共享锁尔后想同时临时更新数据，这时双方都等待对方释放共享锁，死锁就产生了。更新锁没有这个问题，因为某一时刻只有一个事务得到某资源的更新锁。

一个事务里怎么去掉写锁

相关推荐