1

I attended an interview today and came across an interesting question:

What problem, if two request with UPDATE action will execute concurrently in sql?. I knew we have optimistic locking and pessimistic locking approach to resolve this. I am just completely stuck with this idea in my head!

  1. Optimistic locking: During this time, when i check the timestamp (where mytimestamp = oldtimestamp). What happens if request 1 and request 2 will reach to this point at the exact same time (I mean completely exact)? => i am stuck at this point!

  2. Pessimistic locking : (In Exclusive lock mode)

    2.1. When the client looses connection with server, the lock still exist in Db and other client cannot do anything => How to resolve this kind of issue?

    2.2. And also when requests come and acquire the same lock( At read moment ) at the exact same time => what happen in this case?

Please advice on this problem because i am very interested in the solution on how to resolve this in real time scenario.

If you have any question, please add to the comment section.

Edit:

I also refer this article. See my attachment for more clear.

enter image description here

Thank you.

Community
  • 1
  • 1
vietvoquoc
  • 813
  • 1
  • 10
  • 23
  • 2
    2 thiings arriving at the exact same time is not possible. – juergen d Oct 19 '15 at 09:20
  • Which DB it was , as in oracle/teradata/SQL server to avoid locking there are rules for locks and if the client does lost a connection with the server does not lock. Eg in SQL server if you are doing update using select , use NO LOCK option in your select so that it will not lock your table..etc – anwaar_hell Oct 19 '15 at 09:23
  • eg for 2.2 , multiple read can happen at same time without lock . Also read can be done with write going on the same table [Dirty Read] – anwaar_hell Oct 19 '15 at 09:24
  • thank for your comment but i just wanna clarify it! i think this situation still exist but rarely, i also reference this post http://dba.stackexchange.com/questions/9813/how-sql-server-handles-concurrent-requests – vietvoquoc Oct 19 '15 at 09:25
  • I think you read about oracle locking here ...http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm – anwaar_hell Oct 19 '15 at 09:26
  • by the way you didnt mentioned the db as this may happen with us in our next interview...:) – anwaar_hell Oct 19 '15 at 09:27
  • Like i mentioned in title => this is question on SQL. Thank you. – vietvoquoc Oct 19 '15 at 09:28
  • If you got any help from my comments accept my answer...:) – anwaar_hell Oct 19 '15 at 09:30
  • "this is question on SQL" doesn't help when determining the DB engine. You just specified the *language* (which is actually irrelevant to your question). If you mean MS SQL server, use the sql-server tag. Oracle, MySQL, Postgres... all of those also use SQL, and there's plenty of differences to be had. – Luaan Oct 19 '15 at 11:17
  • Is there any advice? – vietvoquoc Oct 20 '15 at 02:08

0 Answers0