Transactions in SQL
In the process of this exercise, we observe how a given type of the table lock level interacts with the blocking that sets out the transaction at the query.
Select the table that contains the dictionary with the column names.
Write a query that will change the name in the directory row in the SQL window: convert ii from uppercase to lowercase and vice versa.
[code]
UPDATE Applications
SET AName = Upper(AName)
WHERE ID = 1
UPDATE Applications
SET AName = LOWER(AName)
WHERE ID = 1
[/code]
Put this query in a transaction.
[code]
BEGIN TRANSACTION
UPDATE Applications
SET AName = Upper(AName)
WHERE ID = 1
UPDATE Applications
SET AName = LOWER(AName)
WHERE ID = 1
COMMIT TRANSACTION
[/code]
Check the transactions available at the server using the system view sys.dm_tran_active_transactions in a new SQL window.
[code]SELECT * FROM sys.dm_tran_active_transactions[/code]
Check the lock resources available on the server using the system view sys.dm_tran_locks in a new SQL window.
[code]SELECT * FROM sys.dm_tran_locks [/code]
Run the transaction opening, update, and display the table with SELECT statement, without a transaction completion. Make sure the update is reflected for the transaction.
[code]
BEGIN TRANSACTION
UPDATE Applications
SET AName = Upper(AName)
WHERE ID = 1
UPDATE Applications
SET AName = LOWER(AName)
WHERE ID = 1
SELECT * FROM Applications
[/code]
- Print the same table with the lock 7 in the new SQL window.
- Missing(NOLOCK). Check the changes.
- Row locks ROWLOCK or default (READCOMMITTED). Make sure the window goes into standby mode.
[code]
BEGIN TRANSACTION
INSERT INTO Applications (ID, AName, AVersion) VALUES (4, ‘Photoshop’, 2015)
WAITFOR DELAY ’00:01′
ROLLBACK
SELECT * FROM Applications
SELECT * FROM Applications WITH (NOLOCK)
SELECT * FROM Applications WHERE ID = 2
SELECT * FROM Applications WHERE AName = ‘Photoshop’
[/code]
Check the transactions available at the server using the system view sys.dm_tran_active_transactions in the SQL window. You should see a new user transaction.
Check the locks on resources available on the server using system view sys.dm_tran_locks in the SQL window. The lock associated with active table should be added. Determine which resource is locked.
[code]
SELECT b.name, c.name, a.*
FROM sys.dm_tran_locks a
INNER JOIN sys.databases b ON a.resource_database_id = database_id
INNER JOIN sys.objects c ON a.resource_associated_entity_id = object_id
[/code]
Roll back the transaction in the window — use only the ROLLBACK statement. The output of the table using SELECT statement should show the difference with the upgrade.
[code]
BEGIN TRANSACTION
UPDATE Applications
SET AName = Upper(AName)
WHERE ID = 3
UPDATE Applications
SET AName = LOWER(AName)
WHERE ID = 3
ROLLBACK TRANSACTION
SELECT * FROM Applications
[/code]
After re-output of the view, a user transaction in the SQL window should disappear.
[code]SELECT * FROM sys.dm_tran_active_transactions [/code]
After re-output of the view, a lock connected with the current table in the SQL window should disappear.
[code]
SELECT b.name, c.name, a.*
FROM sys.dm_tran_locks a
INNER JOIN sys.databases b ON a.resource_database_id = database_id
INNER JOIN sys.objects c ON a.resource_associated_entity_id = object_id
[/code]
The contents of the table involved with the canceled update should finally appear in the SQL window.
Thanks for your attention!
Need MySQL Homework Help?
One of our experts has completed a task related to SQL transaction in order to help you to improve your skills in SQL. If you need help with your homework, AssignmentShark provides SQL homework help and assistance with case studies. Our service delivers solutions that serve as an educational basis for students who have problems with completing their assignments on MySQL. Our experts will provide prompt help with any kind of homework strictly in accordance with your requirements. We don’t store completed homework and don’t resell to other students. Each homework assignment is completed individually for each student by the expert in the particular discipline.
We know that IT is a complex area of knowledge, so we have gathered professionals that can help you in various fields of database optimization, data analysis, QA and software texting, etc. As for our rates, we consider them affordable for students. Our bidding system allows you to pick an expert with the most suitable parameters concerning experience, rating, number of completed works, and the price for your particular assignment. You will have the opportunity to control the work progress and stay in touch with the expert using the online chat. All payments are conducted only for completed parts of your order, so you don’t need to pay for a pig in a poke. Got stuck with homework? Take the opportunity to reduce stress from studying and get online assignment help right now!