how to lock database until transaction complete

Question

1.00/5 (1 vote)

See more:

Hi I need to lock all tables in my database the moment i start transaction

VB

Try
   If con.State = ConnectionState.Closed Then con.Open()
   tran = con.BeginTransaction()
   cmd.Transaction = tran
   'I need to lock database until tran commit or rollback
   cmd.CommandText = "select max(id)+1 from table_1;"
   cmd.ExecuteNonQuery()
   'Do some stuff
   tran.Commit() ' realease database lock
Catch ex As Exception
   MsgBox(ex.Message)
   tran.Rollback()  ' realease database lock
Finally
   con.Close()
End Try

Posted 5-Apr-15 8:46am

M. Rawan

Add a Solution

Comments

Sergey Alexandrovich Kryukov 5-Apr-15 15:00pm

Why? Why do you think the mechanism of transaction itself is insufficient?
—SA

M. Rawan 5-Apr-15 17:01pm

transaction does not lock table on a select command.
the application work's fine for a single user, but with multi-user i faced some conflict , so i thought locking tables on transactions should solve any conflict

Sergey Alexandrovich Kryukov 5-Apr-15 20:35pm

Transaction does not exactly lock database, but it ensures isolation of updates of the database in part related to the transaction. Effectively, it makes access to some relevant part of data single-user (one user at a time). It guards database consistency at all times.
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Answer 1 · 2015-04-05T09:39:00

Solution 1

To put it simply, a database-transaction is exactly that: Locking the database until commit or rollback. It wouldn't make sense if I wrote down here what has been written down many times already; please refer to these links and pay attention especially to the aspect of "Transaction Isolation Level":

http://en.wikipedia.org/wiki/Database_transaction[^]
http://en.wikipedia.org/wiki/Isolation_%28database_systems%29[^]
Google search: vb.net transaction tutorial[^]
Google search: Codeproject transaction tutorial[^]

If you are going to implement an application that accesses a database simultaneously with multiple threads or in concert with other applications I would recommend you restrict yourself to the Isolation-level "Serializable". As a beginner it's definitely the safest option to not risk allowing anything unwanted to happen.

Posted 5-Apr-15 9:39am

Sascha Lefèvre

Comments

M. Rawan 5-Apr-15 16:57pm

Hi Sascha Lefévre
I thought that executing several commands in a transaction should lock the database until transaction commit or rollback
but The fact is the table does not lock until an update command (a select command locks nothing).
i tried setting Isolation-level to "Serializable" and i got same result.

Sascha Lefèvre 5-Apr-15 17:10pm

Hi Sirwan Mualla
What exactly are you trying to do and in which way does the result not match your expectation? (Going to bed now, so my next response will take a bit.. :-)

M. Rawan 5-Apr-15 19:49pm

there are too many long processes in the app (each process have a transaction) but if the transaction does not lock the database it could lead to an error.

Sascha Lefèvre 6-Apr-15 1:57am

1) Could it be that your long processes are not each encapsulated in a single transaction? E.g. a process reads a value in transaction #1, transaction #1 terminates, the process does some stuff and then starts a transaction #2 for writing something?

2) Have you double-checked that you are indeed using IsolationLevel.Serializable everywhere and not missed one code-part where you begin a transaction?

3) How long are these long processes taking to complete, approximately?

Sergey Alexandrovich Kryukov 5-Apr-15 20:37pm

Agree, a 5. Our discussion with the inquirer in the comments to the question probably suggests that he might have some misconception of the nature of transaction.
—SA

Sascha Lefèvre 6-Apr-15 1:51am

Thank you, Sergey. Yes, probably..