3

Does a transaction lock my table when I'm running multiple queries?

Example: if another user will try to send data in same time which I use transaction, what will happen?

Also how can I avoid this, but also to be sure that all data has inserted successfully into database?

Begin Tran;
    Insert into Customers (name) values(name1);

    Update CustomerTrans 
    set CustomerName = (name2);

Commit;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
daa sssa
  • 35
  • 1
  • 1
  • 3
  • 1
    That's what transactions do, they lock the table(s). You can't avoid Table Locking, it's there by design. – Thom A Jul 13 '18 at 11:54
  • https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries – DeshDeep Singh Jul 13 '18 at 11:55
  • I also suggest using a `TRY...CATCH` with a `ROLLBACK`, not just a `BEGIN TRANSACTION...COMMIT` with no error handling. – Thom A Jul 13 '18 at 11:55
  • Possible duplicate of [Understanding SQL Server LOCKS on SELECT queries](https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries) – DeshDeep Singh Jul 13 '18 at 11:56
  • You have no where clause in your update, that is why it is update the whole table every time and hence locking down the whole table too. Try adding some sort of where clause in your update statement. – M.Ali Jul 13 '18 at 12:02
  • The problem its not the where statement, the problem is that i dont want any case that it will execute only first query.Example a bad network connection. I want to prevent sql making only insert statement. If i will use rollback is it better? – daa sssa Jul 13 '18 at 12:04
  • 1
    SQL Server Transactions has ACID property and data is never left in the half-done state. So as long as you are using transactions, you dont have to worry about data being left half-done or corrupted. The ACID property of SQL Server Transactions makes sure that "All is done or nothing is done". You will have to trust sql server on this. – M.Ali Jul 13 '18 at 12:09
  • Thank you that its the answer i was need it. But the final question is, can two clients use Transaction in same table in same time? – daa sssa Jul 13 '18 at 12:12
  • @daasssa, much depends on the granularity of locking, Since you have no `WHERE` clause on the `UPDATE`, every row in the table will be updated and locked until the transaction commits. But if you have a `WHERE` clause and useful index, only those rows will be locked. – Dan Guzman Jul 13 '18 at 12:17
  • Thank you very much Dan Guzman your answer is very usefull to me!!!( Now understand how locking works!) – daa sssa Jul 13 '18 at 12:19

2 Answers2

1

You have to implement transaction smartly. Below are some performance related points :-

  1. Locking Optimistic/Pessimistic. In pessimistic locking whole table is locked. but in optimistic locking only specific row is locked.
  2. Isolation level Read Committed/Read Uncommitted. When table is locked it depends upon on your business scenario if it allowed you then you can go for dirty read using with NoLock.
  3. Try to use where clause in update and do proper indexing. For any heavy query check the query plan.
  4. Transaction timeout should be very less. So if the table is locked then it should throw error and In catch block you can retry.

These are few points you can do.

Deepak Kumar
  • 648
  • 6
  • 14
  • 2
    The second item is often the solution in larger environments, especially for stored procedures reporting against sparsely updated/historical data. Unfortunately, and all too often, people see the use of NO LOCK and perceive it as a performance boost when in fact it is a "last resort" mechanism to avoid deadlocks. – Ross Bush Jul 13 '18 at 12:38
0

You cannot avoid that multiples users load data to the database. It is neither feasible nor clever to lock every time a single user requested the usage of a table. Actually you do not have to worry about it, because the DB itself will provide mechanism to avoid such issues. I would recommend you reading into ACID properties.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

What may happen is that you could suffer a ghost read, which basically consist that you cannot read data unless the user who is inserting data commits. And even if you have finished inserting data and do not commit, there is a fair chance that you will not see the changes.

DDL operations such as creation, removal, etc. are themselves committed at the end. However DML operation, such as update, insert, delete, etc. are not committed at the end.

Thomas
  • 90
  • 1
  • 7
  • Show transaction doesnt prevent other users to use transaction in same table right? – daa sssa Jul 13 '18 at 12:11
  • Depending upon the type of locking. There are different locking scopes. From a single register, row to the entire table. – Thomas Jul 13 '18 at 12:34