LOCK DB WHEN INSERTING

Hello.

I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that.

Thanks.

You don't need to lock the db, just lock the database objects (accounting table in this case). You can use SqlTransaction to control the locks in your code, with setting the IsolationLevel according to your requirement. Keep in mind that you should commit or rollback the opened transaction after you finish/cancel using the database resource, otherwise it may give rise to database blocking/deadlock issues. For example:

connection.Open();

SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;

transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"SampleTransaction");

Here are some useful links:

SqlTransaction Class:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Isolation levels:

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

|||

Good.

I'm doing some research about that.

Assuming I'm using sqldatasource, the code below should work?

sqlReceiver.InsertCommand =

"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; " & insertCommand &"SET TRANSACTION ISOLATION LEVEL READ COMMITTED; "
sqlReceiver.Insert()

I couldn't find any property to use with sqldatasource for that purpose.

Thanks!

|||Good Your sql command should work. Unfortunately it seems there is no property to set the isolation level in SqlDataSource.|||

I believe you can have the sqldatasouce's insert command join a transaction in _Inserting, and commit it in _Inserted.

Or maybe not. You should be able to catch the insert attempt in _Inserting, create your own connection, start a transaction, steal the inserting's command object (With the parameters already set up for you), then execute it, commit the transaction, and set "e.cancel=true" to cancel the original inserting request.

Or, you can put the whole thing in a stored procedure, and call it from the sqldatasource, that works too.

Or you can just wrap your original Insert statement like you did (Just put it in the sqldatasource's insertcommand property along with your insert statement). That works as well.

|||

Good, thanks for the answers.

I was monitoring the commands via Sql Server Profiler. I thought I would be able to see theSET TRANSACTION ISOLATION LEVEL SERIALIZABLEcommand being executed there, but I couldn't. That made me think that the command was not being executed.

Now, I'm using something like that to set the isolation level:

(...)
transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable)

Any additional comments?

Thanks.

|||

Hey,

about my problem, I still have it. Let me explain.

I'm doing several SELECTS, to get the balances from different accounts. So, until I finish that and insert the new records, the table would be really locked. I couldn't do that using a transaction level isolation. Well, I guess I can do, but that would make me to rewrite lots of code.

So, it's possible to lock the entire table with one command, and unlock only after another command?

Thanks.

|||

sqldatasource1.selectcommand="BEGIN TRANSACTION SELECT TOP 0 * FROM Table1 WITH (TABLOCKX,HOLDLOCK) More SQL Statements COMMIT TRANSACTION"

With that said, you are probably coding your T-SQL poorly, and could do what you want to do in one or two statements. There are also a lot better ways of handling the locking than this as well. This will cause concurrency issues, and you will probably start hitting deadlock issues if you use this technique very much.