Wpf C＃SQL server一般问题 [英] Wpf C# SQL server general question

问题描述

Hello Fellow Programmers，



我想知道社区对记录锁定的看法......见下文：



问题：锁定记录的最佳方法是什么，以便应用程序知道您正在编辑的特定记录是LOCKED，并且不允许其他终端的其他用户编辑同一记录SQL数据库？



示例：客户销售系统/我用新地址更新客户X.使用相同数据库/应用程序的另一个位置的另一个人试图编辑客户X的地址记录。



目标：使用上面的示例，其他用户会收到一条消息框或警告，表示记录已被锁定。



我喜欢避免使用数据库作为决定因素的方法，但我知道如果没有其他方法可以做到这一点。



我尝试过的事情：



数据库中用于收集从中传递的记录锁的表更新按钮单击应用程序，但我不喜欢它。

Hello Fellow Programmers,

I wanted to see what the community has to say about record locking... See below:

Question: What is the best way to "lock" a record so the application knows that the specific record you are editing is LOCKED and doesn't allow another user at another terminal to edit the same record in the SQL database?

Example: Customer Sales System / I'm updating customer X with a new address. Another person at another location using the same database/application tries to "edit" the address record for customer X.

Goal: Using the example above, the "other user" would receive a messagebox or alert that says record already locked.

I'd love a way to avoid using the database as the deciding factor, but I understand if there's no other way to do it.

What I have tried:

A table in the database to collect record locks passed from the "update" button click in the application, but I don't like it.

推荐答案

引用：

我喜欢一种避免使用数据库作为决定因素的方法，但我知道如果没有别的方法可以做到这一点。

I'd love a way to avoid using the database as the deciding factor, but I understand if there's no other way to do it.

ok ..但那就是数据库擅长的。你可以自己写一个类似的方案 - 但最终，我怀疑你最终会得到一些与数据库提供的东西相当的东西（这对你来说并不令人讨厌或不尊重（*） - 那就是承认削减的工作量已经进入Oracle，MSSQL等）



让我们说客户记录中的每一行都有一个'id'字段

- 是如果记录未被其他人编辑，则为空白

- 终端/用户编辑记录的终端/用户ID



这是当你将'id'字段设置为终端/用户说这个终端想要更新记录以及另一个请求可能会说'我想要'时，仍然可以在线程术语中获得相当于'竞争条件'的条件对那条记录做点什么'。如果您正在使用存储过程，审计和日志记录，那么如果出现不一致，那么您可以解决它，但我仍然认为让db执行db所做的事情应该是经验法则 - 除非当然，db不提供任何形式的行锁定...



另一个问题是在胁迫下，我想我甚至读过MSSQL可能不会接受请求。



我的0.02c值



（*）除非你为上述任何一家公司工作/工作

ok .. but thats what databases are good at. You could write a similar scheme yourself - but at the end of the day, I doubt you would end up with something comparable to what the DB offers (that's not being nasty or disrespectful to you (*) - thats acknowledging the shear amount of work that has gone into Oracle, MSSQL etc)

Lets say every row in your customer record has a 'id' field that
- is blank if the record isnt being edited by someone else
- has the terminal/user id of the terminal/user editing the record

It's still possible to get the equivalent of a 'race condition' in threading terms between when you set the 'id' field to the terminal/user to say 'this terminal wants to update the record' and when another request might say 'I want to do something to that record'. If you're using stored procedures, auditing and logging, then should an inconsistency arise then you can resolve it, but I still think 'let the db do what the db does' should be the rule of thumb - unless of course, the db doesnt offer any form of row locking ...

The other issue is under duress, I think I read even MSSQL might not honour the request.

my 0.02c worth

(*) unless you work/worked for any of the aforementioned companies

请参阅本文中的使用锁定分区一章：

SQL Server事务锁定和行版本控制指南 [ ^ ]



示例A ：

See the chapter "Working with Lock Partitioning" in this article:
SQL Server Transaction Locking and Row Versioning Guide[^]

Example A:

BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
        FROM TestTable
        WITH (HOLDLOCK);

和自定义锁定和行版本控制：

And chapter "Customizing Locking and Row Versioning":

Quote：

在SQL Server中，使用sys.dm_os_waiting_tasks动态管理视图来确定是否进程被阻止，谁阻止它。在早期版本的SQL Server中，使用sp_who系统存储过程。

In SQL Server, use the sys.dm_os_waiting_tasks dynamic management view to determine whether a process is being blocked and who is blocking it. In earlier versions of SQL Server, use the sp_who system stored procedure.

我要感谢你们的评论。我已经对他们进行了评论，我决定采用Garth发布的变体。我创建了一个使用表名作为变量的SP，并更新了一个新列，表明记录已锁定或未锁定

I want to thank both of you for your comments. I have reviewed them both and I have decided to go with a variation of what Garth posted. I have created an SP that uses the table name as a variable and updates a new column that indicates the record is "locked" or "unlocked"

这篇关于Wpf C＃SQL server一般问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！