MS Server - Lock a record
-
Hello Experts, I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time. Thanks a lot. Revision Table ID | Lock | ------------ 1 | False | 2 | False | 3 | False | Lets say that 2 computer are using same program connecting to the same server database Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true) Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock This is my question. same request Client:1 & 2 - Request Time: 10:30:00 - Query is (SELECT TOP 1 * FROM Table WHERE Lock = False) - Put ID to a variable - In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting
-
Hello Experts, I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time. Thanks a lot. Revision Table ID | Lock | ------------ 1 | False | 2 | False | 3 | False | Lets say that 2 computer are using same program connecting to the same server database Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true) Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock This is my question. same request Client:1 & 2 - Request Time: 10:30:00 - Query is (SELECT TOP 1 * FROM Table WHERE Lock = False) - Put ID to a variable - In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting
-
Hello Experts, I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time. Thanks a lot. Revision Table ID | Lock | ------------ 1 | False | 2 | False | 3 | False | Lets say that 2 computer are using same program connecting to the same server database Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true) Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock This is my question. same request Client:1 & 2 - Request Time: 10:30:00 - Query is (SELECT TOP 1 * FROM Table WHERE Lock = False) - Put ID to a variable - In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting
I think you will need to write your own locking mechanism. No big deal.
-
Hello Experts, I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time. Thanks a lot. Revision Table ID | Lock | ------------ 1 | False | 2 | False | 3 | False | Lets say that 2 computer are using same program connecting to the same server database Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true) Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock This is my question. same request Client:1 & 2 - Request Time: 10:30:00 - Query is (SELECT TOP 1 * FROM Table WHERE Lock = False) - Put ID to a variable - In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting
hansoctantan wrote:
is it possible that more than one user can access the same record in this process?
Probably. Does not mean that it will be a problem. There's a lot of stuff written on locking in SQL, with topics like lost updates[^] and dirty reads (no, not those magazines). You can add various hints[^] to the query. MSDN has dedicated a section[^] to consistency and concurrency.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Hello Experts, I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time. Thanks a lot. Revision Table ID | Lock | ------------ 1 | False | 2 | False | 3 | False | Lets say that 2 computer are using same program connecting to the same server database Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true) Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock This is my question. same request Client:1 & 2 - Request Time: 10:30:00 - Query is (SELECT TOP 1 * FROM Table WHERE Lock = False) - Put ID to a variable - In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting
-
Hello Experts, I have a table ID, Lock and other fields. I have a VB.NET program that gets the TOP 1 record that is not lock and lock that record to be keyed/entry. Multiple users will use this program, is it possible that more than one user can access the same record in this process? Its possible users will request a record at the same time. Thanks a lot. Revision Table ID | Lock | ------------ 1 | False | 2 | False | 3 | False | Lets say that 2 computer are using same program connecting to the same server database Client:1 - Request Time: 10:30:00 will get the ID 1, ID 1 will lock(update to true) Client:2 - Request Time: 10:30:05 will get the ID 2, ID 2 will lock(update to true) - cause ID 1 is lock This is my question. same request Client:1 & 2 - Request Time: 10:30:00 - Query is (SELECT TOP 1 * FROM Table WHERE Lock = False) - Put ID to a variable - In the above run time, Is the 2 client will get the ID 1 or the this query will stack on SQL Server will finish one query first before executing another query?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind. - Most of the problem in your life are due to two reasons: you act without thinking, or think without acting
If I understood your question correctly, you're wondering if session 1 can read the data that session 2 is locking. If this is the concern then the answer in many cases is no. Consider the following scenario:
Session 1 Session 2
Select record A, field named SomeValue is 1
Select record A, field named SomeValue is 1Begin transaction
Update record A,
field named SomeValue is set to 2,
an eXclusive lock is takenSelect record A, record is locked, session 2 waitsSome other modifications
Session 2 still waitsCommit the transaction, lock is freed
Session 2 now gets the answer, field named SomeValue is 2 (the value session 1 updated)Now the actual sequence varies depending if auto-commit is on and so on. Also you should know that if row versioning is in effect, then the behaviour is far different. For more information, read Data versioning in SQL Server using row versions[^]