Perhaps this question is not very clear but I didn't find better words for the heading, which describes the problem I like to deal with shortly.
也许这个问题不是很清楚,但我没有找到更好的词汇,它描述了我想要处理的问题。
I want to restrict access from a java desktop application to postgres.
我想限制从java桌面应用程序访问postgres。
The background:
Suppose you have 2 apps running and the first Application has to do some complex calculations on the basis of data in the db. To nail the immutability of the data in the db down i'd like to lock the db for insert, update and delete operations. On client side i think it's impossible to handle this behaviour satisfactory. So i thought about to use a little java-app on server-side which works like a proxy. So the task is to hand over CRUD (Create Read Update Delete) operations until it gets a command to lock. After a lock it rejects all CUD operations until it gets a unlock command from the locking client or a timeout is reached.
假设您有2个应用程序正在运行,并且第一个应用程序必须根据数据库中的数据执行一些复杂的计算。为了确定数据库中数据的不变性,我想锁定数据库以进行插入,更新和删除操作。在客户端,我认为不可能满足这种行为。所以我想在服务器端使用一个像代理一样工作的小应用程序。因此,任务是移交CRUD(创建读取更新删除)操作,直到它获得锁定命令。锁定后,它会拒绝所有CUD操作,直到它从锁定客户端获得解锁命令或达到超时。
Questions:
What do you think about this approach?
您如何看待这种方法?
Is it possible to lock a Database while using such an approach?
使用这种方法时是否可以锁定数据库?
Would you prefer Java SE or Java EE as server-side java app?
您是否更喜欢Java SE或Java EE作为服务器端Java应用程序?
Thanks in advance.
提前致谢。
3 个解决方案
#1
3
Why not use transactions in your operations? The database has features to maintain data integrity itself, rather than resorting to a brute operation such as a total-database lock.
为什么不在运营中使用交易?数据库具有维护数据完整性的功能,而不是采用诸如全数据库锁之类的粗暴操作。
This locking mechanism you describe sounds like it would be a pain for the users. Are the users initating the lock or is the software itself? If it's the users, you can expect some problems when Bob hits lock and then goes to lunch for 2 hours, forgetting to unlock the database first...
您描述的这种锁定机制听起来像对用户来说会很痛苦。用户是在启动锁定还是软件本身?如果是用户,当Bob遇到锁定然后去吃午饭2小时,忘记首先解锁数据库时,你会遇到一些问题......
#2
1
Indeed... there are a few proper ways to deal with this problem.
确实......有一些正确的方法可以解决这个问题。
- Just lock the tables in your code. Postgresql has commands for locking entire tables that you could run from your client application
- Pick a transaction isolation level that doesn't have the problem of reading data that was committed after your txn started (BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ).
只需锁定代码中的表即可。 Postgresql具有锁定可以从客户端应用程序运行的整个表的命令
选择一个事务隔离级别,该级别没有读取在txn启动后提交的数据的问题(BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ)。
Of these, by far the most efficient is to use repeatable read as your isolation level. Postgres supports this quite efficiently, and it will give you a consistent view of the data without such heavy locking of the db.
其中,最有效的方法是使用可重复读取作为隔离级别。 Postgres非常有效地支持这一点,它将为您提供一致的数据视图,而不会对数据库进行如此严格的锁定。
#3
0
Year i thought about transactions but in this case i can't use them. I'm sorry i didn't mention it exactly. So assume the follow easy case: A calculation closes one area of responsibility. After calc a new one is opened and new inserts are dedicated to it. But while calculation-process a insert or update or delete is not allowed to the data of the (currently calculated) area of responsibility. More over a delete is strictly prohibited because data has to be archived.
一年我考虑交易,但在这种情况下,我不能使用它们。对不起,我没有提到。因此,假设以下简单案例:计算结束了一个责任范围。在计算之后,打开一个新的并且新的插入物专用于它。但是,在计算过程中,不允许对(当前计算的)责任区域的数据进行插入或更新或删除。严禁删除更多内容,因为必须存档数据。
So imo the use of transactions doesn't fit this requirement. Or did i miss sth.?
因此,交易的使用不符合这一要求。或者我错过了吗?
ps: (off topic) @jsight: i currently read that intenally postgres mapps "repeatable read" to "serializable", so using "repeatable read" gets you more restriction then you would perhaps expect.
ps :(关闭主题)@jsight:我目前正在阅读那些特意postgres mapps“可重复读取”到“可序列化”,所以使用“可重复读取”会让你获得更多限制,然后你可能会期望。
#1
3
Why not use transactions in your operations? The database has features to maintain data integrity itself, rather than resorting to a brute operation such as a total-database lock.
为什么不在运营中使用交易?数据库具有维护数据完整性的功能,而不是采用诸如全数据库锁之类的粗暴操作。
This locking mechanism you describe sounds like it would be a pain for the users. Are the users initating the lock or is the software itself? If it's the users, you can expect some problems when Bob hits lock and then goes to lunch for 2 hours, forgetting to unlock the database first...
您描述的这种锁定机制听起来像对用户来说会很痛苦。用户是在启动锁定还是软件本身?如果是用户,当Bob遇到锁定然后去吃午饭2小时,忘记首先解锁数据库时,你会遇到一些问题......
#2
1
Indeed... there are a few proper ways to deal with this problem.
确实......有一些正确的方法可以解决这个问题。
- Just lock the tables in your code. Postgresql has commands for locking entire tables that you could run from your client application
- Pick a transaction isolation level that doesn't have the problem of reading data that was committed after your txn started (BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ).
只需锁定代码中的表即可。 Postgresql具有锁定可以从客户端应用程序运行的整个表的命令
选择一个事务隔离级别,该级别没有读取在txn启动后提交的数据的问题(BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ)。
Of these, by far the most efficient is to use repeatable read as your isolation level. Postgres supports this quite efficiently, and it will give you a consistent view of the data without such heavy locking of the db.
其中,最有效的方法是使用可重复读取作为隔离级别。 Postgres非常有效地支持这一点,它将为您提供一致的数据视图,而不会对数据库进行如此严格的锁定。
#3
0
Year i thought about transactions but in this case i can't use them. I'm sorry i didn't mention it exactly. So assume the follow easy case: A calculation closes one area of responsibility. After calc a new one is opened and new inserts are dedicated to it. But while calculation-process a insert or update or delete is not allowed to the data of the (currently calculated) area of responsibility. More over a delete is strictly prohibited because data has to be archived.
一年我考虑交易,但在这种情况下,我不能使用它们。对不起,我没有提到。因此,假设以下简单案例:计算结束了一个责任范围。在计算之后,打开一个新的并且新的插入物专用于它。但是,在计算过程中,不允许对(当前计算的)责任区域的数据进行插入或更新或删除。严禁删除更多内容,因为必须存档数据。
So imo the use of transactions doesn't fit this requirement. Or did i miss sth.?
因此,交易的使用不符合这一要求。或者我错过了吗?
ps: (off topic) @jsight: i currently read that intenally postgres mapps "repeatable read" to "serializable", so using "repeatable read" gets you more restriction then you would perhaps expect.
ps :(关闭主题)@jsight:我目前正在阅读那些特意postgres mapps“可重复读取”到“可序列化”,所以使用“可重复读取”会让你获得更多限制,然后你可能会期望。